Re: need simplified update query

[Chris Stubben]

Hi,

This should work. Just keep the conditionals out of the where clause...


create table tmp(id1 int, id2 int, id3 int);
insert into tmp values (1,2,3);
insert into tmp values (2,3,5);


select * from tmp;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1 | 2 | 3 |
| 2 | 3 | 5 |
+------+------+------+


update tmp set id1=CASE when id1=2 then 0 else id1 end,
id2=CASE when id2=2 then 0 else id2 end,
id3=CASE when id3=2 then 0 else id3 end;

select * from tmp;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1 | 0 | 3 |
| 0 | 3 | 5 |
+------+------+------+