MYSQL, what I want is like
update tablename
case fieldA
when value1 then set fieldX0=xxx,fieldX1=bbb,fieldX2=ccc ...
when value2 then set fieldY0=yyy,fieldY1=eee,fieldY2=fff ...
end
what is the right and simple syntax for it? thank you very much.
It should be written this way:
UPDATE tablename
SET fieldX = CASE WHEN fieldA = 'value1' THEN 'xxx' ELSE fieldX END,
fieldY = CASE WHEN fieldA = 'value2' THEN 'yyy' ELSE fieldY END
WHERE fieldA IN ('value1', 'value2');
Note that: I wrote the ELSE part this way, because the default for the ELSE is NULL if the condition of the CASE expression is not valid, so this will set it to the original value not to the NULL value.
