mysql update column with value from another table

Question

I have two tables, both look like

id  name  value
===================
1   Joe     22
2   Derk    30

I need to copy the value of value from tableA to tableB based on the check name in each table.

Do you have any tips for this UPDATE statement?

Answer 1

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

Answer 2

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'