CODEWITHSUNDEEP
mysql
zuckerbrot
zuckerbrot

Reputation: 117

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?

Upvotes: 0

Views: 400

Answers (2)

helmis.m
helmis.m

Reputation: 244

Try this

UPDATE table1
SET table1.val1 = 0, table1.val2 = 0 , table1.val3 = 0
INNER JOIN table2 ON table1.foreign_key = table2.key

May you have null value on table2.key

Upvotes: 0

Jens
Jens

Reputation: 69440

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

Upvotes: 2

Related Questions