SQLite, MySQL, PHP: Ternary operator (IF() statement) in MySQL and SQLite SQLite, MySQL, PHP: ternarni operator (IF () izjava) u MySQL i SQLite

Posted on 03. Poslano na 03. Feb, 2010 by Dragos in Coding , MySQL , PHP , SQLite Veljača, 2010 mimo Dragos u kodiranju, MySQL, PHP, SQLite

While working on a proxy checker tool for one of my projects, I was struggling on how to execute a conditional query on my SQLite database to update a column only if its value is greater than 0 (it would be pointless in my case to let the script update the column with negative values). Tijekom rada na proxy checker alat za jedan od mojih projekata, bio sam na bore kako da izvrši uvjetnih upita na moj SQLite baza podataka za ažuriranje stupac samo ako je njezina vrijednost veća od 0 (to bi bilo besmisleno u mom slučaju na to da skripta ažurirati kolona s negativnim vrijednostima).

In Mysql one would do like this (we use the ternary operator IF(to_check_expression>0,expression1,expression2) ): In MySQL jedan bi se ovako (mi koristimo ternarni operator IF (to_check_expression> 0, expression1, expression2)):

update table_name set column_name=IF((column_name-1)>0,(column_name-1),0)

However, in SQLite this method will not work. Međutim, u SQLite ta metoda neće raditi. Instead I had to use the case when (condition) then expression1 else expression2 end method. Umjesto toga sam morao iskoristiti slučaj kada je (stanje) onda expression1 else expression2 kraj metoda.

The above query in MySQL can be rewritten like this in SQLite: Iznad upit u MySQL se može napisati kao što je ovaj u SQLite:

update table_name set column_name=case when (column_name-1)>0 then (column_name-1) else 0 end

That's it. That's it. I hope it helps someone! Nadam se to pomoć netko!