How to Update a MySQL Column with Values from Another Table?

MySQL Database: Update column using value from another table

The goal of this article is to populate the "value" column in "tableB" with the corresponding value in "tableA" based on the matching name.

UPDATE statement syntax:

In order to update the "value" column in "tableB", we can use the following UPDATE statement:

<code class="language-sql">UPDATE tableB
SET tableB.value = (
  SELECT a.value
  FROM tableA a
  WHERE a.name = tableB.name
)
WHERE tableB.name IN (
  SELECT name
  FROM tableA
);</code>

Instructions:

• The internal SELECT query finds the corresponding "value" in "tableA" based on the matching "name".
• The outer UPDATE statement updates the "value" column in "tableB" with the found value.
• The WHERE clause in the outer UPDATE statement ensures that only matching records are updated.

Example:

Using sample data:

<code class="language-sql">UPDATE tableB
SET tableB.value = (
  SELECT a.value
  FROM tableA a
  WHERE a.name = tableB.name
)
WHERE tableB.name IN (
  SELECT name
  FROM tableA
);</code>

After executing this statement, "tableB.value" will be updated as follows:

<code>id  name  value
===================
1   Joe     22
2   Derk    30</code>

Other options:

• To update only specific rows, you can add additional conditions in the WHERE clause.
• To dynamically update "tableB.value" based on "tableA.value", you can use an IF statement in the SET clause, as shown in the example.

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