mysql update column with value from another table

Question

I have two tables, both look like idnamevalue===================1Joe222Derk30 I need to copy the value of value from tableA to tableB based on the checked name in each table . Any tips for this UPDATE statement?

P粉186904731 · Answer

You need to join two tables:

For example, you want to copy the value of name from tableA to tableB, 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

P粉511985082 · Answer

In addition to this answer, if you need to dynamically change tableB.value based on tableA.value, you can do the following:

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'