IF() statement alternative in SQLite

by

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”

More Related Contents:

  1. SQLite DateTime comparison
  2. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  3. What are valid table names in SQLite?
  4. Deleting duplicate rows from sqlite database
  5. What does the SQL Standard say about usage of backtick(`)?
  6. Delete column from SQLite table
  7. Declare variable in SQLite and use it
  8. sqlite database default time value ‘now’
  9. How do I insert datetime value into a SQLite database?
  10. How to get a list of column names on Sqlite3 database?
  11. PostgreSQL IF statement
  12. basic recursive query on sqlite3?
  13. IF EXISTS condition not working with PLSQL
  14. SQLite and custom order by
  15. SQLite auto-increment non-primary key field
  16. GROUP_CONCAT in SQLite
  17. How to do IF NOT EXISTS in SQLite
  18. Core Data Performance with Single Parent Entity
  19. Match only entire words with LIKE?
  20. What is causing Foreign Key Mismatch error?
  21. How to split comma-separated values?
  22. sqlite select with condition on date
  23. How to compare sqlite TIMESTAMP values
  24. SQLite Order By Date1530019888000
  25. SQL – Inserting a row and returning primary key
  26. How to select the first N rows of each group?
  27. Which SQL statement is faster? (HAVING vs. WHERE…)
  28. UPSERT *not* INSERT or REPLACE
  29. Drop column from SQLite table
  30. converting int to real in sqlite

Leave a Comment