CODEWITHSUNDEEP
phpmysqldatabase
SGuerreiro
SGuerreiro

Reputation: 57

MYSQL updating with condition

Good afternoon, I've been searching for a way to update a table with different conditions and with one row only. I understand i can use the method "case" but its only updating the last row.

UPDATE inscription SET
classification= CASE WHEN id_inscription =1 THEN 1 END,
classification= CASE WHEN id_inscription =17 THEN 2 END,
classification= CASE WHEN id_inscription =18 THEN 3 END
WHERE id_inscription BETWEEN 1 AND 20;

I'm not sure if there is other way without using the "else" condition or if it is a query error.

Thank you for your time

Upvotes: 0

Views: 28

Answers (1)

juergen d
juergen d

Reputation: 204746

Use a single case with multiple conditions. That is how it is meant to be used

UPDATE inscription 
SET classification = CASE WHEN id_inscription =  1 THEN 1 
                          WHEN id_inscription = 17 THEN 2     
                          WHEN id_inscription = 18 THEN 3 
                     END
WHERE id_inscription BETWEEN 1 AND 20;

Upvotes: 1

Related Questions