Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

IF() statement alternative in SQLite

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?

like image 658
VeroLom Avatar asked Feb 02 '11 12:02

VeroLom


People also ask

Is there an if statement in SQLite?

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.

How to use if ELSE in SQLite?

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.

Does SQLite support CASE?

SQLite provides two forms of the CASE expression: simple CASE and searched CASE .

How do I use Isnull in SQLite?

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.


2 Answers

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"

like image 71
osgx Avatar answered Sep 20 '22 00:09

osgx


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;
like image 25
Lukasz Szozda Avatar answered Sep 22 '22 00:09

Lukasz Szozda