Is something wrong with this MySQL query?

Question

Hey guys,

I'm trying to update a site that was coded horribly, and I think there is an error in this multiple IF statement. I've been Googling for a while and can't find any example of multiple IFs in a MySQL UPDATE query.

The idea is, we want to update a certain column of the row based on a different column of the row.

Here's the query:

$sql = "UPDATE `pet_colors` AS c
SET c.inshop = 
IF(c.rarity='1', '25',
IF(c.rarity='2', '10',
IF(c.rarity='3', '3', '1')))
WHERE c.species='{$p->species}' AND c.buyable='1' LIMIT ".rand(1,3));

Any help is greatly appreciated.

Answer 1

Why not use a CASE statement instead of the imbricated IFs? It would be "as good" (or as bad, question of perspective), but easier to read.

[•••] SET c.inshop = CASE c.rarity WHEN '1' THEN '25' WHEN '2' THEN '10' WHEN '3' THEN '3' ELSE '1' END [•••]