Menu
It seems I have big problems with conditional queries.
I have to do a conditional update. I write here what I would like to do:
IF(SELECT tipo FROM abbonamento WHERE idU = 17) = 'punti' THEN
UDPATE abbonamento SET punti = punti - 1
ELSE
UPDATE abbonamento SET bonus = bonus - 1
Obviously this doesn't work.
Any idea?
637
Yes! This works because MySQL doesn't update the row, if there is no change, as mentioned in docs: If you set a column to the value it currently has, MySQL notices this and does not update it. Yes!
Update with conditionWHERE clause can be used with SQL UPDATE to add conditions while modifying records. Without using any WHERE clause, the SQL UPDATE command can change all the records for the specific columns of the table.
The syntax for the IF-THEN-ELSE statement in MySQL is: IF condition1 THEN {... statements to execute when condition1 is TRUE...} [ ELSEIF condition2 THEN {...
MySQL supports IF statement.
UPDATE abbonamento
SET punti = IF(tipo = 'punti', punti - 1, punti),
bonus = IF(tipo <> 'punti', bonus - 1, bonus)
WHERE id = 17
or you can also use CASE
UPDATE abbonamento
SET punti = CASE WHEN tipo = 'punti' THEN punti - 1 ELSE punti END,
bonus = CASE WHEN tipo <> 'punti' THEN bonus - 1 ELSE bonus END
WHERE id = 17
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
