update - upsert sql
SQLite-UPSERT*not* INSERT or REPLACE (12)
Is there some clever way to do this in SQLite that I have not thought of?
Basically I want to update three out of four columns if the record exists, If it does not exists I want to INSERT the record with the default (NUL) value for the fourth column.
The ID is a primary key so there will only ever be one record to UPSERT.
(I am trying to avoid the overhead of SELECT in order to determin if I need to UPDATE or INSERT obviously)
I cannot confirm that Syntax on the SQLite site for TABLE CREATE. I have not built a demo to test it, but It doesnt seem to be supported..
If it was, I have three columns so it would actually look like:
CREATE TABLE table1( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, Blob1 BLOB ON CONFLICT REPLACE, Blob2 BLOB ON CONFLICT REPLACE, Blob3 BLOB );
but the first two blobs will not cause a conflict, only the ID would So I asusme Blob1 and Blob2 would not be replaced (as desired)
UPDATEs in SQLite when binding data are a complete transaction, meaning Each sent row to be updated requires: Prepare/Bind/Step/Finalize statements unlike the INSERT which allows the use of the reset function
The life of a statement object goes something like this:
- Create the object using sqlite3_prepare_v2()
- Bind values to host parameters using sqlite3_bind_ interfaces.
- Run the SQL by calling sqlite3_step()
- Reset the statement using sqlite3_reset() then go back to step 2 and repeat.
- Destroy the statement object using sqlite3_finalize().
UPDATE I am guessing is slow compared to INSERT, but how does it compare to SELECT using the Primary key?
Perhaps I should use the select to read the 4th column (Blob3) and then use REPLACE to write a new record blending the original 4th Column with the new data for the first 3 columns?
Beginning with version 3.24.0 UPSERT is supported by SQLite.
From the documentation:
UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL. UPSERT syntax was added to SQLite with version 3.24.0 (pending).
An UPSERT is an ordinary INSERT statement that is followed by the special ON CONFLICT clause
Image source: https://www.sqlite.org/images/syntax/upsert-clause.gif
Eric B’s answer is OK if you want to preserve just one or maybe two columns from the existing row. If you want to preserve a lot of columns, it gets too cumbersome fast.
Here’s an approach that will scale well to any amount of columns on either side. To illustrate it I will assume the following schema:
CREATE TABLE page ( id INTEGER PRIMARY KEY, name TEXT UNIQUE, title TEXT, content TEXT, author INTEGER NOT NULL REFERENCES user (id), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Note in particular that
name is the natural key of the row –
id is used only for foreign keys, so the point is for SQLite to pick the ID value itself when inserting a new row. But when updating an existing row based on its
name, I want it to continue to have the old ID value (obviously!).
I achieve a true
UPSERT with the following construct:
WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) ) INSERT OR REPLACE INTO page (id, name, title, content, author) SELECT old.id, new.name, new.title, old.content, new.author FROM new LEFT JOIN page AS old ON new.name = old.name;
The exact form of this query can vary a bit. The key is the use of
INSERT SELECT with a left outer join, to join an existing row to the new values.
Here, if a row did not previously exist,
old.id will be
NULL and SQLite will then assign an ID automatically, but if there already was such a row,
old.id will have an actual value and this will be reused. Which is exactly what I wanted.
In fact this is very flexible. Note how the
ts column is completely missing on all sides – because it has a
DEFAULT value, SQLite will just do the right thing in any case, so I don’t have to take care of it myself.
You can also include a column on both the
old sides and then use e.g.
COALESCE(new.content, old.content) in the outer
SELECT to say “insert the new content if there was any, otherwise keep the old content” – e.g. if you are using a fixed query and are binding the new values with placeholders.
Assuming 3 columns in the table.. ID, NAME, ROLE
BAD: This will insert or replace all columns with new values for ID=1:
INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, 'John Foo', 'CEO');
BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:
INSERT OR REPLACE INTO Employee (id, role) VALUES (1, 'code monkey');
GOOD: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be default (NULL).
INSERT OR REPLACE INTO Employee (id, role, name) VALUES ( 1, 'code monkey', (SELECT name FROM Employee WHERE id = 1) );
This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.
INSERT OR REPLACE INTO Employee (id, name, role) VALUES ( 1, 'Susan Bar', COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer') );
Expanding on Aristotle’s answer you can SELECT from a dummy 'singleton' table (a table of your own creation with a single row). This avoids some duplication.
I've also kept the example portable across MySQL and SQLite and used a 'date_added' column as an example of how you could set a column only the first time.
REPLACE INTO page ( id, name, title, content, author, date_added) SELECT old.id, "about", "About this site", old.content, 42, IFNULL(old.date_added,"21/05/2013") FROM singleton LEFT JOIN page AS old ON old.name = "about";
Having just read this thread and been disappointed that it wasn't easy to just to this "UPSERT"ing, I investigated further...
You can actually do this directly and easily in SQLITE.
Instead of using:
INSERT OR REPLACE INTO
This does exactly what you want it to do!
Here is a solution that really is an UPSERT (UPDATE or INSERT) instead of an INSERT OR REPLACE (which works differently in many situations).
It works like this:
1. Try to update if a record with the same Id exists.
2. If the update did not change any rows (
NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0)), then insert the record.
So either an existing record was updated or an insert will be performed.
The important detail is to use the changes() SQL function to check if the update statement hit any existing records and only perform the insert statement if it did not hit any record.
One thing to mention is that the changes() function does not return changes performed by lower-level triggers (see http://sqlite.org/lang_corefunc.html#changes), so be sure to take that into account.
Here is the SQL...
--Create sample table and records (and drop the table if it already exists) DROP TABLE IF EXISTS Contact; CREATE TABLE [Contact] ( [Id] INTEGER PRIMARY KEY, [Name] TEXT ); INSERT INTO Contact (Id, Name) VALUES (1, 'Mike'); INSERT INTO Contact (Id, Name) VALUES (2, 'John'); -- Try to update an existing record UPDATE Contact SET Name = 'Bob' WHERE Id = 2; -- If no record was changed by the update (meaning no record with the same Id existed), insert the record INSERT INTO Contact (Id, Name) SELECT 2, 'Bob' WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0); --See the result SELECT * FROM Contact;
--Create sample table and records (and drop the table if it already exists) DROP TABLE IF EXISTS Contact; CREATE TABLE [Contact] ( [Id] INTEGER PRIMARY KEY, [Name] TEXT ); INSERT INTO Contact (Id, Name) VALUES (1, 'Mike'); INSERT INTO Contact (Id, Name) VALUES (2, 'John'); -- Try to update an existing record UPDATE Contact SET Name = 'Bob' WHERE Id = 3; -- If no record was changed by the update (meaning no record with the same Id existed), insert the record INSERT INTO Contact (Id, Name) SELECT 3, 'Bob' WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0); --See the result SELECT * FROM Contact;
I think this may be what you are looking for: ON CONFLICT clause.
If you define your table like this:
CREATE TABLE table1( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, field1 TEXT );
Now, if you do an INSERT with an id that already exists, SQLite automagically does UPDATE instead of INSERT.
INSERT OR REPLACE is NOT equivalent to "UPSERT".
Say I have the table Employee with the fields id, name, and role:
INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO") INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")
Boom, you've lost the name of the employee number 1. SQLite has replaced it with a default value.
The expected output of an UPSERT would be to change the role and to keep the name.
If you are generally doing updates I would ..
- Begin a transaction
- Do the update
- Check the rowcount
- If it is 0 do the insert
If you are generally doing inserts I would
- Begin a transaction
- Try an insert
- Check for primary key violation error
- if we got an error do the update
This way you avoid the select and you are transactionally sound on Sqlite.
The best approach I know is to do an update, followed by an insert. The "overhead of a select" is necessary, but it is not a terrible burden since you are searching on the primary key, which is fast.
You should be able to modify the below statements with your table & field names to do what you want.
--first, update any matches UPDATE DESTINATION_TABLE DT SET MY_FIELD1 = ( SELECT MY_FIELD1 FROM SOURCE_TABLE ST WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY ) ,MY_FIELD2 = ( SELECT MY_FIELD2 FROM SOURCE_TABLE ST WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY ) WHERE EXISTS( SELECT ST2.PRIMARY_KEY FROM SOURCE_TABLE ST2 ,DESTINATION_TABLE DT2 WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY ); --second, insert any non-matches INSERT INTO DESTINATION_TABLE( MY_FIELD1 ,MY_FIELD2 ) SELECT ST.MY_FIELD1 ,NULL AS MY_FIELD2 --insert NULL into this field FROM SOURCE_TABLE ST WHERE NOT EXISTS( SELECT DT2.PRIMARY_KEY FROM DESTINATION_TABLE DT2 WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY );
You can indeed do an upsert in SQLite, it just looks a little different than you are used to. It would look something like:
INSERT INTO table name (column1, column2) VALUES ("value12", "value2") WHERE id = 123 ON CONFLICT DO UPDATE SET column1 = "value1", column2 = "value2" WHERE id = 123
SELECT COUNT(*) FROM table1 WHERE id = 1;
COUNT(*) = 0
INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3);
COUNT(*) > 0
UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;