mysql where then,MySQL CASE ... WHERE ... THEN语句

I have a MySQL UPDATE statement that uses a CASE clause

UPDATE partsList SET quantity =

CASE

WHEN partFK = 1 THEN 4

WHEN partFK = 2 THEN 8

END

WHERE buildFK = 1;

The above statement works. Yet when I remove one of the WHEN statements, it breaks and the error indicates the CASE clause isn't returning anything. Is it that the CASE clause must have more than one WHEN to function.

I don't know beforehand how many updates I'll need, so I'm trying to build a single update statement that can handle one or many updates.

Thanks for any insights you can provide.

解决方案

It isn't that the CASE must have more than one, WHEN...THEN, it's that it must handle all the date you give it.

If you removed one of the clauses, you leave a hole. e.g.

UPDATE partsList SET quantity =

CASE

WHEN partFK = 1 THEN 4

END

WHERE buildFK = 1;

With this update statement, if parkFK is 2, then the update fails because the CASE can't handle the input.

You can either limit your source data by adding another line to your where-clause (e.g. AND partFK in (1,2)), or you could add an ELSE to the case expression.

UPDATE partsList SET quantity =

CASE

WHEN partFK = 1 THEN 4

WHEN partFK = 2 THEN 8

ELSE 12

END

WHERE buildFK = 1;

However, based on the SQL statement you've shown, there is probably a better way. Presumably, partFK is a foreign-key to some other table. Can you pull the value for quantity from there?