Having the following table:

CREATE TABLE "table2" (
    "field1"    INTEGER NOT NULL,
    "field2"    BLOB NOT NULL,
    "oldInfo"   INTEGER NOT NULL CHECK("oldInfo" >= 0 AND "oldInfo" <= 1), --0 means False, 1 means True
    FOREIGN KEY("field1") REFERENCES "table1"("field1"),
    PRIMARY KEY("field1","oldInfo")
);

I have the following use cases ("oldInfo" field will always be set to zero):

1. First time insert for the pair (field1, oldInfo).

2. Second time insert for the pair (field1, oldInfo). The existing oldInfo field shall be set to one before the insertion of the new row due to the composite key.

3. Third and subsequent insertions for the pair (field1, oldInfo). The row for that field1 where oldInfo is one shall be deleted and then, the row for that field1 where oldInfo is zero shall be modified to have oldInfo set to one, all before the insertion of the new row.

Can I do it with standard SQL into one query without using triggers?

I am using sqlite3 for now, but I would like to have queries that works for every DBMS.

Only one process can access the database at a time, there is no concurrency.

CodePudding user response：

If you want do it on database level you have to do it in trigger or do it through function/procedure.

Othervise you have to do it on application level.