IF() statement alternative in SQLite

Question

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?

Answer 1

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"

Answer 2

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;

Answer 3

UPDATE pages
SET rkey = rkey + 2,
    lkey = IF(lkey >= $key, lkey + 2, lkey)
WHERE rkey >= $key

??? it to

UPDATE pages
SET lkey = lkey + 2
WHERE rkey >= $key AND lkey >= $key

UPDATE pages
SET rkey = rkey + 2,
WHERE rkey >= $key

Isn't it better?