Is there any single SQL statement equivalent to these?
UPDATE table_name SET (a = 'something', b='B1') WHERE id=1;
UPDATE table_name SET (a = 'something else', b='B2') WHERE id=2;
UPDATE table_name SET (a = 'another', b='B3') WHERE id=3;
-
What's wrong with executing three separate statements? Unless you're seeing particularly high latency to your server, this shouldn't be an issue.Michael Mior– Michael Mior2011-11-21 04:40:25 +00:00Commented Nov 21, 2011 at 4:40
Add a comment
|
3 Answers
Yes, this:
UPDATE table_name
SET a = CASE WHEN id = 1
THEN 'something'
WHEN id = 2
THEN 'something else'
WHEN id = 3
THEN 'another'
END
WHERE id IN (1,2,3)
;
but I'm not sure if it's what you have in mind?
4 Comments
Moe Sweet
Thanks, what about multiple fields (see edited question), can we wrap fields within CASE block or do we need to write different CASE blocks for each field? My query is going to update 5 fields. Thanks again.
Adam Wenger
The OP also wants to update b, you will need a second
CASE for that.Adam Wenger
You would need 5
CASE statements for your 5 fields.
ruakh
@MoeSweet: You'll need separate CASE expressions, since MySQL doesn't support the
SET (a, b) = (...) syntax and doesn't support CASE expressions that return multiple values.If you have bigger array of data to be inserted, then you may use ON DUPLICATE KEY UPDATE construction. It will work more efficient in MySQL.
See my answer here for the similar question, for the example of usage.
Comments
You can use select rows with your constant value, then join it with you table.
UPDATE table_name T INNER JOIN (select 1 as id,'something' as a,'B1' as b
union all select 2,'something','B2'
union all select 3,'another','B2') V on T.id = V.id set T.a = V.a, T.b = V.b;