CODEWITHSUNDEEP
sqlsqlite
Elye
Elye

Reputation: 60161

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?

Upvotes: 1

Views: 1087

Answers (1)

CL.
CL.

Reputation: 180080

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.)

Upvotes: 1

Related Questions