SQLite Insert and Replace with condition

问题:

I can not figure out how to query a SQLite. needed: 1) Replace the record (the primary key), if the condition (comparison of new and old fields entries) 2) Insert an entry if no such entry exists in the database on the primary key.

Importantly, it has to work very fast! I can not come up with an effective inquiry.

Edit.

MyInsertRequest - the desired expression.

Script:

CREATE TABLE testtable (a INT PRIMARY KEY, b INT, c INT)

INSERT INTO testtable VALUES (1, 2, 3)

select * from testtable
1|2|3

-- Adds an entry, because the primary key is not
++ MyInsertRequest VALUES (2, 2, 3) {if c>4 then replace}

select * from testtable
1|2|3
2|2|3

-- Adds
++ MyInsertRequest VALUES (3, 8, 3) {if c>4 then replace}

select * from testtable
1|2|3
2|2|3
3|8|3

-- Does nothing, because such a record (from primary key field 'a') 
-- is in the database and none c>4
++ MyInsertRequest VALUES (1, 2, 3) {if c>4 then replace}

select * from testtable
1|2|3
2|2|3
3|8|3

-- Does nothing
++ MyInsertRequest VALUES (3, 34, 3) {if c>4 then replace}

select * from testtable
1|2|3
2|2|3
3|8|3

-- replace, because such a record (from primary key field 'a') 
-- is in the database and c>2
++ MyInsertRequest VALUES (3, 34, 1) {if c>2 then replace}

select * from testtable
1|2|3
2|2|3
3|34|1

回答1:

Isn't INSERT OR REPLACE what you need ? e.g. :

INSERT OR REPLACE INTO table (cola, colb) values (valuea, valueb)

When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally.

You have to put the condition in a unique constraint on the table. It will automatically create an index to make the check efficient.

e.g.

-- here the condition is on columnA, columnB
CREATE TABLE sometable (columnPK INT PRIMARY KEY,
    columnA INT,
    columnB INT,
    columnC INT,
    CONSTRAINT constname UNIQUE (columnA, columnB)
)

INSERT INTO sometable VALUES (1, 1, 1, 0);
INSERT INTO sometable VALUES (2, 1, 2, 0);

select * from sometable
1|1|1|0
2|1|2|0

-- insert a line with a new PK, but with existing values for (columnA, columnB)
-- the line with PK 2 will be replaced
INSERT OR REPLACE INTO sometable VALUES  (12, 1, 2, 6)

select * from sometable
1|1|1|0
12|1|2|6

回答2:

Assuming your requirements are:

• Insert a new row when a doesn't exists;
• Replacing row when a exist and existing c greater then new c;
• Do nothing when a exist and existing c lesser or equal then new c;

INSERT OR REPLACE fits first two requirements.

For last requirement, the only way I know to make an INSERT ineffective is supplying a empty rowset.

A SQLite command like following whould make the job:

INSERT OR REPLACE INTO sometable SELECT newdata.* FROM
    (SELECT 3 AS a, 2 AS b, 1 AS c) AS newdata
    LEFT JOIN sometable ON newdata.a=sometable.a
    WHERE newdata.c<sometable.c OR sometable.a IS NULL;

New data (3,2,1 in this example) is LEFT JOINen with current table data.

Then WHERE will "de-select" the row when new c is not less then existing c, keeping it when row is new, ie, sometable.* IS NULL.

回答3:

I tried the others answers because I was also suffering from a solution to this problem.

This should work, however I am unsure about the performance implications. I believe that you may need the first column to be unique as a primary key else it will simply insert a new record each time.

INSERT OR REPLACE INTO sometable
    SELECT columnA, columnB, columnC FROM (
    SELECT columnA, columnB, columnC, 1 AS tmp FROM sometable
        WHERE sometable.columnA = 1 AND
              sometable.columnB > 9
    UNION
        SELECT 1 AS columnA, 1 As columnB, 404 as columnC, 0 AS tmp)
    ORDER BY tmp DESC
    LIMIT 1

In this case one dummy query is executed and union-ed onto a second query which would have a performance impact depending on how it is written and how the table is indexed. The next performance problem has potential where the results are ordered and limited. However, I expect that the second query should only return one record and therefore it should not be too much of a performance hit.

You can also omit the ORDER BY tmp LIMIT 1 and it works with my version of sqlite, but it may impact performance since it can end up updating the record twice (writing the original value then the new value if applicable).

The other problem is that you end up with a write to the table even if the condition states that it should not be updated.