I have the code for MySQL (perl):
UPDATE pages
SET rkey = rkey + 2,
lkey = IF(lkey >= $key, lkey + 2, lkey)
WHERE rkey >= $key
I need to use this code with SQLite, but there is no support of IF() function. What I can do?
How iif() Works in SQLite. In SQLite, iif() is a conditional function that returns the second or third argument based on the evaluation of the first argument. It's logically equivalent to CASE WHEN X THEN Y ELSE Z END . iif() is an abbreviation for Immediate IF.
If the result is true , the IIF() function returns the value of the second expression ( true_expression ). Otherwise, it returns the value of the third expression ( false_expression ). In practice, you use the IIF() function to add the if-else logic to queries to form more flexible queries.
SQLite provides two forms of the CASE expression: simple CASE and searched CASE .
SQLite ifnull() functionThe ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.
For generic SQL you can use CASE
:
CASE is used to provide if-then-else type of logic to SQL. Its syntax is:
SELECT CASE ("column_name") WHEN "condition1" THEN "result1" WHEN "condition2" THEN "result2" ... [ELSE "resultN"] END FROM "table_name"
From http://www.sqlite.org/lang_expr.html section "The CASE expression"
E.g.
UPDATE pages SET rkey = rkey + 2, lkey = CASE WHEN lkey >= $key THEN lkey + 2 ELSE lkey END WHERE rkey >= $key
Another link about SQLite & CASE (with example of update with subselect) http://sqlite.awardspace.info/syntax/sqlitepg09.htm
CASE can be used in UPDATE in generic SQL, but I have no info about SQLite support of UPDATEs with CASE
http://www.craigsmullins.com/ssu_0899.htm section "Using CASE Expressions When Modifying Data"
SQLite version 3.32.0 and newer support IIF
.
iif(X,Y,Z)
The iif(X,Y,Z) function returns the value Y if X is true, and Z otherwise.
The iff(X,Y,Z) function is logically equivalent to and generates the same bytecode as the CASE expression "CASE WHEN X THEN Y ELSE Z END".
E.g.
UPDATE pages
SET rkey = rkey + 2,
lkey = IIF(lkey >= $key, lkey + 2, lkey)
WHERE rkey >= $key;
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