mysql update if set_一个查询中的MySQL更新条件（UPDATE，SET和CASE）

I'm trying to update a set of records (boolean fields) in a single query if possible.

The input is coming from paginated radio controls, so a given POST will have the page's worth of IDs with a true or false value.

I was trying to go this direction:

UPDATE my_table

SET field = CASE

WHEN id IN (/* true ids */) THEN TRUE

WHEN id IN (/* false ids */) THEN FALSE

END

But this resulted in the "true id" rows being updated to true, and ALL other rows were updated to false.

I assume I've made some gross syntactical error, or perhaps that I'm approaching this incorrectly.

Any thoughts on a solution?

解决方案

Didn't you forget to do an "ELSE" in the case statement?

UPDATE my_table

SET field = CASE

WHEN id IN (/* true ids */) THEN TRUE

WHEN id IN (/* false ids */) THEN FALSE

ELSE field=field

END

Without the ELSE, I assume the evaluation chain stops at the last WHEN and executes that update. Also, you are not limiting the rows that you are trying to update; if you don't do the ELSE you should at least tell the update to only update the rows you want and not all the rows (as you are doing). Look at the WHERE clause below:

UPDATE my_table

SET field = CASE

WHEN id IN (/* true ids */) THEN TRUE

WHEN id IN (/* false ids */) THEN FALSE

END

WHERE id in (true ids + false_ids)