1.11M Members

Update rows which don't match WHERE also

 
0
 

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.
:S

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

Thanks in advance!

 
0
 

This probably (untested):

UPDATE db.table
SET col1 = IF(col3 IN ('a', 'b', 'c') AND col2 = 'something', 1, 0)
 
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!

 
-1
 

Just test it. If you notice a performance issue, you can always insert a (temporary) index.

 
0
 

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...

Question Answered as of 10 Months Ago by pritaeas
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: