How to Update a MySQL Column's Value Based on Data from Another Table?

MySQL: Update column value based on data from another table

Question:

Suppose there are two tables with similar structures:

id	name	value
1	Joe	22
2	Derk	30

The task is to update the "value" column in table B with the value in table A based on the matching "name" value.

Solution:

You can use the SQL UPDATE statement with a JOIN clause to perform this update operation. Here is a possible query:

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

This query will update the "value" column of all rows in table B that match table A, based on the "name" column.

Dynamic value update:

You can modify the query to dynamically update table B.value based on the values ​​in table A.value. For example, the following query only updates table B.value if table A.value is greater than 0:

<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>

In this example, the "value" column in the row with "name" "Joe" in Table B will be updated with the value from Table A only if the value in Table A is greater than 0. Otherwise, the existing values ​​in Table B will remain unchanged.

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