Hello everyone!
Is it possible to write a query which does the following :

UPDATE db.table 
SET col1 = 1 
WHERE col2='something' AND col3 IN ('a','b','c')
ELSE SET col1 = 0

Meaning that the rows that don't match the where conditions will also be updated to another value.
Note that db.table has no primary key, but foreign key only.

edit : I am looking for a single query of course!

Thanks in advance!

Edited by ktsangop

4 Years
Discussion Span
Last Post by ktsangop

This probably (untested):

UPDATE db.table
SET col1 = IF(col3 IN ('a', 'b', 'c') AND col2 = 'something', 1, 0)

That's working just fine!

This is probably another thread's question but would there be a problem in large data sets, considering that there is no primary key, and the "ELSE" condition could contain a lot of un-indexed rows?

Thank you!


Thanks, i will try to insert some dummy data and test it.
It's just that i've always wondered why we are able to leave a table key-less anyway...

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.