Member Avatar for ktsangop

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!

Edited by ktsangop
  • 2 Contributors
  • 4 Replies
  • 236 Views
  • 36 Minutes Discussion Span
  • Latest Post Latest Post by ktsangop
Member Avatar for pritaeas

This probably (untested):

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

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 for pritaeas

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

Member Avatar for ktsangop

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.