Conditional Update in SQLite

Conditional update in SQLite

A CASE statement should work with the following syntax:

UPDATE
  books
SET number_of_page = CASE WHEN number_of_pages > 0 THEN (number_of_pages - 1) ELSE 0 END
WHERE whatever_condition

Is there a way to conditionally change which sqlite table you are updating?

I would approach it with 2 update scripts

UPDATE table
SET name = 'hello'
WHERE id = 3;

UPDATE table2
SET name = 'hello'
WHERE id = 3
AND 0 = (SELECT COUNT(1) FROM table WHERE id = 3);

How to conditionally INSERT OR REPLACE a row in SQLite?

SQL

INSERT OR REPLACE INTO UserProgress (id, status, level) SELECT 'id value', 'status value', 'level value'
WHERE NOT EXISTS (SELECT * FROM UserProgress WHERE id = 'id value' AND status = 'COMPLETE');

(where id value, status value and level value are inserted as appropriate)

Demo

http://www.sqlfiddle.com/#!5/a9b82d/1

Explanation

The EXISTS part is used to find out whether there is an existing row in the table with the same id whose status value is 'COMPLETE'. If so, nothing is done (due to the WHERE NOT). Otherwise, the row with the specified id is either INSERTed if not present or UPDATEd with the specified values if present (due to the INSERT OR REPLACE).

conditional UPDATE sqlite statement

(Assuming you mean conditional UPDATE, not INSERT)

Fortuitously, it seems you need to reset test2 to null where the join fails, so you can do an update with the set specified as a subquery:

update test2
set value = 
(SELECT t1.value
 FROM test1 t1 where t1.key = test2.key
 LIMIT 1);

SqlFiddle:

The LIMIT will ensure just one row returned, but if the relationship between test1 and test2 isn't 1:1 you will need to apply logic to determine how to join the two tables.

Conditional INSERT or UPDATE in SQLITE

1. Make sure the table has some unique constraint, such as bookID INTEGER PRIMARY KEY.

2. Use INSERT OR REPLACE INTO ... with the insert syntax you have. If the insert would result in a constraint violation (e.g. bookID already exists), the conflicting rows are first removed and the data is then inserted.

Also consider using prepared statements and ? placeholders for the literals to prevent SQL syntax problems and injection attacks, and to improve performance.

Conditional Select and Update rows with previous/next rows

Use a subquery that returns either the next row if it contains he same value of t or the previous row:

update tablename
set (col2, col3, ..., coln) = ( 
  select tab.col2, tab.col3, ..., tab.coln
  from tablename tab
  where (tab.id = tablename.id + 1 and tab.t = tablename.t) or (tab.id = tablename.id - 1)
  order by tab.id desc limit 1
)  
where (col2, col3, ..., coln) = (0, 0, ..., 0)
  and exists (
        select 1 from tablename tab 
        where  (tab.id = tablename.id + 1 and tab.t = tablename.t) or (tab.id = tablename.id - 1)
      )

See the demo.

SQLite conditionals in creation of a trigger

In SQLite there is no IF conditional statement.

In this case you can use simple WHERE clauses:

CREATE TRIGGER document_handler AFTER UPDATE ON document
FOR EACH ROW
BEGIN
  INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
  SELECT NEW.id, 'type', OLD.type, NEW.type, CURRENT_TIMESTAMP
  WHERE NEW.type IS NOT OLD.type;

  INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
  SELECT NEW.id, 'title', OLD.title, NEW.title, CURRENT_TIMESTAMP
  WHERE NEW.title IS NOT OLD.title;
    
  INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
  SELECT NEW.id, 'path', OLD.path, NEW.path, CURRENT_TIMESTAMP
  WHERE NEW.path IS NOT OLD.path;
END;

I changed all <> with IS NOT which can compare also NULL values.

Also, the equivalent of NOW() in SQLite is CURRENT_TIMESTAMP.

See a simplified demo.

---

Related Topics