How to Update MySQL Table Data Based on Another Table's Values?

Update MySQL table data based on values ​​from another table

Suppose you have two tables with similar structure, tableA and tableB:

id	name	value
1	Joe	22
2	Derk	30

The task is to copy the value of the value column of tableA to the value column of tableB based on the matching name values ​​in the two tables.

To do this, you can use the following MySQL UPDATE statement:

<code class="language-sql">UPDATE tableB
INNER JOIN tableA ON tableB.name = tableA.name
SET tableB.value = tableA.value;</code>

This statement performs an INNER JOIN operation between tableA and tableB based on the name column, and updates the value column in tableB with the corresponding value in tableA.

Condition-based dynamic value update:

If you need to dynamically update the value column in tableB based on the value in tableA, you can use the following method:

<code class="language-sql">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';</code>

Here, the IF() function checks whether tableA.value is greater than 0. If true, set tableB.value to the value of tableA.value; otherwise, retain the original value in tableB.

The above is the detailed content of How to Update MySQL Table Data Based on Another Table's Values?. For more information, please follow other related articles on the PHP Chinese website!