mysql update column with value from another table
I have two tables, both looking like
id name value
===================
1 Joe 22
2 Derk 30
I need to copy the value of value
from tableA
to tableB
based on check name in each table.
Any tips for this UPDATE
statement?
In addition to this answer if you need to change tableB.value according to tableA.value dynamically you can do for example:
UPDATE tableB
INNER JOIN tableA ON tableB.name = tableA.name
SET tableB.value = IF(tableA.value > 0, tableA.value, tableB.value)
WHERE tableA.name = 'Joe'
you need to join the two tables:
for instance you want to copy the value of name
from tableA into tableB
where they have the same ID
UPDATE tableB t1
INNER JOIN tableA t2
ON t1.id = t2.id
SET t1.name = t2.name
WHERE t2.name = 'Joe'
UPDATE 1
UPDATE tableB t1
INNER JOIN tableA t2
ON t1.id = t2.id
SET t1.name = t2.name
UPDATE 2
UPDATE tableB t1
INNER JOIN tableA t2
ON t1.name = t2.name
SET t1.value = t2.value
Second possibility is,
UPDATE TableB
SET TableB.value = (
SELECT TableA.value
FROM TableA
WHERE TableA.name = TableB.name
);