SQLite, MySQL, PHP: Ternary operator (IF() statement) in MySQL and SQLite SQLite, MySQL, PHP: trikomponentnih operaterja (IF () stavek) v MySQL in SQLite

Posted on 03. Objavljeno dne 03. Feb, 2010 by Dragos in Coding , MySQL , PHP , SQLite Februar, 2010 z Dragos v 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). Medtem ko delajo na zastopstvo skladiščnik orodje za eno od mojih projektov, sem bil o tem, kako borijo za izvršitev pogojno izraz na mojem SQLite bazo podatkov, da posodobite stolpca samo, če njegova vrednost je večja od 0 (ne bi bilo nesmiselno v mojem primeru pustiti script update kolone z negativne vrednosti).

In Mysql one would do like this (we use the ternary operator IF(to_check_expression>0,expression1,expression2) ): V enem Mysql bi naredil takole (uporabljamo trikomponentnih operator IF (to_check_expression> 0, expression1, izraz2)):

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

However, in SQLite this method will not work. Vendar pa v SQLite ta metoda ne bo delovala. Instead I had to use the case when (condition) then expression1 else expression2 end method. Namesto tega sem moral uporabljati v primeru, ko (pogoj) potem expression1 else izraz2 end metodo.

The above query in MySQL can be rewritten like this in SQLite: Zgoraj poizvedbo v MySQL se lahko izrazi kot je to v SQLite:

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

That's it. To je to. I hope it helps someone! Upam, da nekdo pomaga!