Elye Elye - 1 month ago 8
SQL Question

How to On Conflict Replace with Condition (for SQLite)?

I have an SQL below to create a table. It will replace where the name conflict.

CREATE TABLE IF NOT EXISTS MYTABLE (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
NAME TEXT NOT NULL,
DATE INTEGER NOT NULL,
CONSTRAINT UNIQUE_NAME UNIQUE (NAME) ON CONFLICT REPLACE
)


However, I would like to replace only if the date is newer (bigger in number), or ignore the new row if the date is older. How could I alter my SQL (for SQLite) statement above to achieve that?

CL. CL.
Answer

It is not possible to do this with a table constraint, you have to use triggers instead:

CREATE INDEX just_some_index ON MyTable(Name);

CREATE TRIGGER MyTable_Name_insert_newer
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN (SELECT Date FROM MyTable WHERE Name = NEW.Name) <= NEW.Date
BEGIN
    DELETE FROM MyTable
    WHERE Name = NEW.Name;
END;

CREATE TRIGGER MyTable_Name_insert_older
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN (SELECT Date FROM MyTable WHERE Name = NEW.Name) > NEW.Date
BEGIN
    SELECT RAISE(IGNORE);
END;

(In SQLite, a scalar subquery without a result returns just NULL, so inserting a new row makes both WHEN clauses fail.)