CODEWITHSUNDEEP
mysql
Undefined Variable
Undefined Variable

Reputation: 4267

Using nested case statement in MySQL

I have a query like so:

UPDATE image
SET angle  = CASE  
WHEN id = 1000 
THEN 90  
ELSE 
angle 
END WHERE 
id IN(1000)

As you might have guessed, this SQL is built dynamically so that using a single statement I can update multiple records. What I want is that instead of updating an value directly as say 90 in this case, I want it to be incremental. So I guess I can do:

UPDATE image
SET angle  = CASE  
WHEN id = 1000 
THEN angle + 90    //<-- instead of 90, I use (angle + 90)
ELSE 
angle 
END WHERE 
id IN(1000)

The only other thing I am trying to figure out is that if (angle + 90) >= 360, then I want the angle field to be 0. I am not sure on how to edit the SQL syntax to make this happen. Any pointers are greatly appreciated!

Upvotes: 1

Views: 1558

Answers (2)

creanium
creanium

Reputation: 647

You can indeed do nested CASE statements

UPDATE image
SET angle  = CASE  
WHEN id = 1000 
THEN 
    CASE WHEN angle >= 270 
    THEN 0
    ELSE angle + 90    //<-- instead of 90, I use (angle + 90)
    END
ELSE 
    angle 
END WHERE 
id IN(1000)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

You can use the modulus operator (%). Whenever the expression angle + 90 exceeds 360 degrees, it will wrap back to zero.

UPDATE image
SET angle = CASE  
    WHEN id = 1000 
THEN (angle + 90) % 360
ELSE angle 
END
WHERE id IN(1000)

Upvotes: 3

Related Questions