Update a field thanks to a subquery based on another field

Question

Hi,
I use a MySQL DB, and I would like to update a field in a table based on another. Something like:

UPDATE table1
SET field1 = table2.id
WHERE field2 IN (
    SELECT table2.name
    FROM table2
    );

I know that this query wouldn't work, but here is the idea. Is that even possible to do?

Cheers,
Nicolas.

Answer 1

You can use a correlated sub query as below. This assumes there will be exactly one matching value returned. It will raise an error if more than one matching value is returned or set the field to null if zero are returned. If that last behaviour isn't desirable you will need a where clause.

UPDATE table1
SET field1 = (SELECT DISTINCT table2.ValueColumn 
              FROM table2 
              WHERE table2.JoinColumn = table1.JoinColumn)

Edit

To review records with 0 or more than 1 matches you could use

SELECT table1.JoinColumn, COUNT(DISTINCT table2.ValueColumn)
FROM table1 
LEFT JOIN table2
ON table2.JoinColumn = table1.JoinColumn
GROUP BY table1.JoinColumn
HAVING COUNT(DISTINCT table2.ValueColumn) <> 1