SQLite, MySQL, PHP: Ternary operator (IF() statement) in MySQL and SQLite
Posted on 03. Feb, 2010 by Dragos in Coding, 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).
In Mysql one would do like this (we use the ternary 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. Instead I had to use the case when (condition) then expression1 else expression2 end method.
The above query in MySQL can be rewritten like this in SQLite:
update table_name set column_name=case when (column_name-1)>0 then (column_name-1) else 0 end
That’s it. I hope it helps someone!
Related posts:
-
xboy










































