1,105,633 Community Members

Update rows which don't match WHERE also

Member Avatar
ktsangop
Junior Poster
131 posts since Jan 2010
Reputation Points: 23 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
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!

Member Avatar
pritaeas
mod_pritaeas
11,315 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 156 [?]
Moderator
Featured
Sponsor
 
0
 

This probably (untested):

UPDATE db.table
SET col1 = IF(col3 IN ('a', 'b', 'c') AND col2 = 'something', 1, 0)
Member Avatar
ktsangop
Junior Poster
131 posts since Jan 2010
Reputation Points: 23 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 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!

Member Avatar
pritaeas
mod_pritaeas
11,315 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 156 [?]
Moderator
Featured
Sponsor
 
-1
 

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

Member Avatar
ktsangop
Junior Poster
131 posts since Jan 2010
Reputation Points: 23 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
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 7 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: