MySQL CASE…WHERE…THEN statements

问题:

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.

回答1:

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?

回答2:

Add ELSE NULL or something before the END of the case. See http://dev.mysql.com/doc/refman/5.0/en/case-statement.html for more info.