SQLite - UPSERT *not* INSERT or REPLACE?

Assuming 3 columns in the table.. ID,NAME,ROLE.

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 NAME 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 name from Employee where id = 1),'Benchwarmer') ).

4 +1 brilliant! The embedded select clause gives you the flexibility to override the default ON CONFLICT REPLACE functionality if you need to combine/compare the old value and the new value for any field. – Greg Apr 1 at 16:56 Worked a treat for what I was trying to do.Thanks.

– Teknogrebo May 28 at 15:49 1 +1 from me too, this should be up higher! – RoboTamer Jun 9 at 23:18 2 If the Employee is referenced by other rows with cascading deletion, then the other rows will still be deleted by replacement. – Don Reba Aug 3 at 9:29 1 The last query is not correct.It should be: coalesce((select role from Employee where id = 1),'Benchwarmer') – Viet Nov 21 at 1:09.

The answer currently accepted is incorrect: 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.

1 from me I'm afraid. Yes the accepted answer is wrong, but while your answer points out the problem it isn't an answer either. For an actual answer, see Eric B's clever solution using an embedded coalesce((select ..), 'new value') clause.

Eric's answer needs more votes here I think. – Day Jul 13 at 22:45 3 Indeed. Eric's definitely the best answer and deserves more votes.

That being said, I think that by pointing out the problem, I've contributed a little bit to finding the good answer (Eric's answer came later, and builds upon the sample sql tables in my answer). So not sure if I deserve a -1, but nevermind :) – gregschlom Jul 14 at 20:31 +1 to offset the -1 above. Lol.

Interesting timeline on this thread though. Clearly your answer was over a week before Eric's, but you both answered the question nearly two years after it was asked. +1 for Eric too though for elaborating.

– Rich Sep 2 at 14:56.

If you are generally doing updates I would .. Begin a transaction Do the update Check the rowcount If it is 0 do the insert Commit 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 Commit This way you avoid the select and you are transactionally sound on Sqlite.

Thanks, just asked this @ stackoverflow. Com/questions/2717590/…. +1 from me!

=) – Alix Axel Apr 27 '10 at 0:05 1 If you're going to check the rowcount using sqlite3_changes() on the 3rd step, make sure you don't use DB handle from multiple threads for modifications. – Linulin Jul 2 '10 at 17:31 Wouldn't the following be less wordy yet with the same effect: 1) select id form table where id = 'x' 2) if (ResultSet.rows. Length == 0) update table where id = 'x'; – Florin Jul 12 '10 at 1:23 1 I really wish INSERT OR UPDATE was part of the language – Florin Jul 12 '10 at 1:24.

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 INTO Use: INSERT OR REPLACE INTO This does exactly what you want it to do!

7 -1 INSERT OR REPLACE is not an UPSERT. See gregschlom's "answer" for the reason why. Eric B's solution actually works and needs some upvotes.

– Day Jul 13 at 22:49 -1 Definitively, the right answer is the one of Eric B! – jdramaix Jul 19 at 13:11.

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 rows, 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: INSERT OR REPLACE INTO page (id, name, title, content, author) SELECT old.

Id, new. Name, new. Title, old.

Content, new. Author FROM ( SELECT "about" AS name, "About this site" AS title, 42 AS author ) AS new LEFT JOIN ( SELECT id, name, content FROM page ) AS old ON new.Name = old. Name; 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 new and 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.

I realize this is an old thread but I've been working in sqlite3 as of late and came up with this method which better suited my needs of dynamically generating parameterized queries: insert or ignore into (, , , ...) values(, , , ...); update set =, =, ... where changes()=0 and =; It's still 2 queries with a where clause on the update but seems to do the trick. I also have this vision in my head that sqlite can optimize away the update statement entirely if the call to changes() is greater than zero. Whether or not it actually does that is beyond my knowledge, but a man can dream can't he?

;) For bonus points you can append this line which returns you the id of the row whether it be a newly inserted row or an existing row. Select case changes() WHEN 0 THEN last_insert_rowid() else end.

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

Way to complicated. – frast May 18 '10 at 7:17.

Mosor, I cannot confirm that Syntax on the SQLite site for TABLE CREATE. I have not built a demo to test it, but It doesn't 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) Sambo, 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?

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

Jan 7 '09 at 2:25 @Mosor: -1 from me, sorry. This is the same as issuing a REPLACE statement. – Alix Axel Apr 27 '10 at 0:04 3 -1 because this does a delete and then an insert if the primary key already exists.

– frast May 18 '10 at 7:13.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.