This is basically an extension of Update multiple rows with one query?. I want to be able to update multiple columns of multiple rows at a time, but not necessarily to update every column of every row.
Here is an example command I would like to be able to execute:
UPDATE person
SET name = CASE id
WHEN 1 THEN 'Jim'
WHEN 3 THEN 'Precious'
END,
sex = CASE id
WHEN 1 THEN 'female'
WHEN 2 THEN 'male'
END
WHERE id IN (1,2,3)
However this returns an error, telling me column 'name' cannot be null. So it seem to me that specifying WHERE id IN (x,y,z) means all selected columns must be updated, even though we're indicating which values to place on a case by case basis.
Is there any way around this limitation, like a special notation to indicate "keep the original value"? If not, is there some other way to update select, arbitrary columns of multiple rows in one query?
Martin Smith :
I think you would need to add an Else updating it to its original value.\n\nUPDATE person\nSET name = CASE id\n WHEN 1 THEN 'Jim'\n WHEN 3 THEN 'Precious'\n ELSE name\nEND,\nsex = CASE id\n WHEN 1 THEN 'female'\n WHEN 2 THEN 'male'\n ELSE sex\nEND\nWHERE id IN (1,2,3)\n",
2010-07-16T00:05:55