zuckerbrot zuckerbrot - 2 months ago 7
MySQL Question

MySQL UPDATE query is not working with WHERE IN condition

In query A below:

UPDATE table1
SET table1.val1 = 0
AND table1.val2 = 0
AND table1.val3 = 0
WHERE table1.foreign_key IN (SELECT DISTINCT table2.key FROM table2)


I expected to update
val1
,
val2
and
val3
to 0, for all entries, which I would get from query B:

SELECT *
FROM table1
WHERE table1.foreign_key IN (SELECT DISTINCT table2.key FROM table2)


However, in my case only about a tenth of entries query B returned, were updated in query A.

How can I update all the entries, which query B returns?

Answer Source

The expressions in the SET clause must be separated by comma not by AND

UPDATE table1 t1
SET t1.val1 = 0,
    t1.val2 = 0,
    t1.val3 = 0
WHERE t1.foreign_key IN (SELECT DISTINCT table2.key FROM table2)

In addition to the missing commas, you could also benefit from using a table alias in your update query. An alias frees us from having to repeat the full table name everywhere, leaving the query easier to write and read.

For more informations see the mysql documentation