Update MySQL with if condition

mysql sql sql-update
Martina picture Martina · Feb 21, 2013 · Viewed 93.8k times · Source

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?

Answer

John Woo picture John Woo · Feb 21, 2013

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

Related questions

Multiple Updates in MySQL

I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL? Edit: For example I have the following Name id Col1 Col2 Row1 1 6 1 Row2 2 2 3 …

Read More
mysql update column with value from another table

I have two tables, both looking like id name value =================== 1 Joe 22 2 Derk 30 I need to copy the value of value from tableA to tableB based on check name in each table. Any tips for this UPDATE statement?

Read More
MySQL - UPDATE multiple rows with different values in one query

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand. For instance, three updates into 1 query: UPDATE table_users …

Read More