UPSERT *not* INSERT or REPLACE

Question

http://en.wikipedia.org/wiki/Upsert

Insert Update stored proc on SQL Server

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 exist, I want to INSERT the record with the default (NULL) 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 determine if I need to UPDATE or INSERT obviously)

Suggestions?

---

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 assume 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:

1. Create the object using sqlite3_prepare_v2()
2. Bind values to host parameters using sqlite3_bind_ interfaces.
3. Run the SQL by calling sqlite3_step()
4. Reset the statement using sqlite3_reset() then go back to step 2 and repeat.
5. 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?

Musjid

SQLite - UPSERT available in pre-release refer: sqlite.1065341.n5.nabble.com/… – Sneaky

UPSERT available in version 3.24.0 of SQLite – Teerell

Answer 1

Assuming three 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: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

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.

GOOD but tedious: 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 the 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')
          );

Bigamous

+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. – Coons

If the Employee is referenced by other rows with cascading deletion, then the other rows will still be deleted by replacement. – Starlight

Add a limit clause to the subquery: select name from Employee where id = 1 limit 1. Although SQLite may not throw in an error here, it is good practice to do so because other SQL dialects do require such a limitation. – Bacon

The last query is not correct. It should be: coalesce((select role from Employee where id = 1),'Benchwarmer') – Tehuantepec

Sorry, but maybe author did mistake in description for fourth example? Should be "When ID=1 exists, the ROLE will be unaffected." Because ROLE is the third parameter in fourth query. – Victimize

Could you explain why This will insert or replace all columns with new values for ID=1: is considered BAD in your first example? The command you present there is meant to create a new record with ID 1, name John Foo and role CEO, or overwrite the record whose ID is 1, if it is already there, with that data (assuming the id is the primary key). So, why is it bad if exactly that happens? – Howdah

It's bad because the latter examples allow for "if this attribute isn't set, set it to this value, otherwise leave it be". The difference is that one forcibly overwrites all values, while the other allows finer grained control over what gets overwritten and what gets only set in case of insert. Think: Update the User record for your super admin user. "If the user doesn't exist, i set his level to 'newbie', otherwise i keep his level (super admin, here)." Wouldn't be nice to make your admin a newbie user. </contrived example> :) See also https://mcmap.net/q/64018/-upsert-not-insert-or-replace below. – Rolo

@Cornelius: That's clear, but that's not what happens in the first example. The first example is meant to forcibly set all columns, which is exactly what happens, no matter whether the record is inserted or replaced. So, why is that considered bad? The linked answer also only points out why something bad can happen when specifying a subset of the columns, like in your second example; it doesn't seem to elaborate on any bad effects of what happens in your first example, INSERT OR REPLACE while specifying values for all columns. – Howdah

Note that this solution fire the delete triggers and can also lead to foreign key violation because the row is deleted first on conflict which is not what you expect when doing an UPSERT – Dita

id = unique if it is a primary key so what is bad in replacing? – Garlaand

Hijacking the top comment: UPSERT is now officially supported by SQLite – Transition

@O.R.Mapper - it becomes bad because if extra columns are added to the table later and populated from other calls, then you need to make sure you track down every instance of "insert or replace" and add the extra columns or they will be nulled/set to default – Nonu

Why didn't you add example query for the best solution of them all? – Sightseeing

Answer 2

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.

Bruns

Here's an upsert library for ruby and an upsert library for python – Febrific

But it's true then that INSERT OR REPLACE on a table with two columns (and no nulls) is equivalent to UPSERT. – Sulfanilamide

Agree, it is not 100% upsert, but can like it in some cases as above. So the programmer will require to make its own judgment. – Tactical

@Sulfanilamide no, because delete + insert (which is a replace) is 2 dml statements, with their own triggers for example. It's not the same as 1 update statement only. – Theine

Answer 3

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

Fluid

+1, works great, but add a WHERE name = "about" constraint on the SELECT ... AS old to speed things up. If you have 1m+ rows, this is very slow. – Tincture

Good point, +1 on your comment. I’ll leave that out of the answer though, because adding such a WHERE clause requires just the kind of redundancy in the query that I was trying to obviate in the first place when I came up with this approach. As always: when you need performance, denormalise – the structure of the query, in this case. – Fluid

You can simplify aristotle's example down to this, if you want: INSERT OR REPLACE INTO page (id, name, title, content, author) SELECT id, 'about', 'About this site', content, 42 FROM ( SELECT NULL ) LEFT JOIN ( SELECT * FROM page WHERE name = 'about' ) – Graceless

Clever. I don’t like it as much on second sight as on first, though – because it forces you to write (or bind) the 'about' value twice. If you want to avoid that, there is no other solution than to put it inside the left SELECT in the join. And then you might as well put all the other new values in there too. But it made me notice that there’s no reason to LEFT JOIN on a (SELECT …) – one can just LEFT JOIN page old ON old.name = new.name. D’oh. Also, one can use a CTE to make it cleaner. I’ve updated the answer accordingly. – Fluid

Wouldn't this unnecessarily trigger ON DELETE triggers when it performs a replace (that is, an update)? – Tales

It will certainly trigger ON DELETE triggers. Dunno about unnecessarily. For most users, it’d probably be unnecessary, even unwanted, but maybe not for all users. Likewise for the fact that it will also cascade-delete any rows with foreign keys into the row in question – probably a problem for many users. SQLite has nothing closer to a real UPSERT, unfortunately. (Save for faking it with an INSTEAD OF UPDATE trigger, I guess.) – Fluid

in my answer I've mixed @EricB COALESCE and your WHEN solution, to have the missing update only some rows but insert my values when not found without the pain of lots of SELECTS in COALESCE's – Victoria

@Miquel: The use of COALESCE had been in EricB’s answer before I even wrote mine, and I mentioned from the start that you could use COALESCE anywhere in the SELECT in my example if you need it. But for an UPSERT (which this question was about), my solution actually doesn’t need the COALESCE – unlike EricB’s answer – because the LEFT JOIN does that job. The WITH is actually completely irrelevant, it only makes the query a bit tidier. The trick to my answer is the combination of INSERT SELECT LEFT JOIN. – Fluid

@AristotlePagaltzis as mentioned in my answer, the problem I'm facing with this query is that it is inserting NULL values for old.xxxx columns when the column does not exist, this is the reason why I proposed a COALESCE(old.content, new.content). In this case it picks new.content when old.content does not exist (INSERT case). – Victoria

COALESCE(old.content, new.content) means that new.content will be ignored when old.content has a value. If that’s what you need, fine, but it’s not the question that was originally asked. – Fluid

Answer 4

This answer has been updated and so the comments below no longer apply.

2018-05-18 STOP PRESS.

UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0 (pending) !

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.

alternatively:

Another completely different way of doing this: in my application I set my in memory rowID to be long.MaxValue when I create the row in memory. (MaxValue will never be used as an ID you won't live long enough....) Then if rowID is not that value then it must already be in the database so needs an UPDATE if it is MaxValue then it needs an insert. This is only useful if you can track the rowIDs in your app.

Ablution

INSERT INTO table(...) SELECT ... WHERE changes() = 0; works for me. – Bourassa

Nice and simple, but has a race condition when the row gets removed between the update and the insert, no? – Civism

@Civism What if you run this inside a transaction? – Drool

@Drool actually not sure if that is enough, perhaps it should lock the row? – Civism

This is how the nodejs sqlite-upsert module works. github.com/pbrandt1/sqlite3-upsert/blob/master/index.js – Lytton

Answer 5

If you are generally doing updates I would ..

1. Begin a transaction
2. Do the update
3. Check the rowcount
4. If it is 0 do the insert
5. Commit

If you are generally doing inserts I would

1. Begin a transaction
2. Try an insert
3. Check for primary key violation error
4. if we got an error do the update
5. Commit

This way you avoid the select and you are transactionally sound on Sqlite.

Julio

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. – Surplice

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'; – Buckden

Answer 6

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 <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>; 

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 <primaryKeyValue> end;

Vociferate

Answer 7

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}

---

Example:

CREATE TABLE t1(id INT PRIMARY KEY, c TEXT);
INSERT INTO t1(id, c) VALUES (1,'a'), (2, 'b');
SELECT * FROM t1;


INSERT INTO t1(id, c) VALUES (1, 'c');
-- UNIQUE constraint failed: t1.id

INSERT INTO t1(id, c) VALUES (1, 'c')
ON CONFLICT DO NOTHING;

SELECT * FROM t1;

INSERT INTO t1(id, c)
VALUES (1, 'c')
ON CONFLICT(id) DO UPDATE SET c = excluded.c;

SELECT * FROM t1;

db<>fiddle demo

Tweeddale

Android is still at 3.19 as of API 27 – Blowtorch

Answer 8

Updates from Bernhardt:

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 (id, column1, column2) 
VALUES ("youruuid", "value12", "value2")
ON CONFLICT(id) DO UPDATE 
SET column1 = "value1", column2 = "value2"

Blowtorch

Just wanna point out to android programmers (and maybe to others as well) that "upsert" syntax is from 2018 (Sqlite 3.24). So unless you are using the lastest API, you will not have this available. – Hoeg

Answer 9

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

Test update:

--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;

Test insert:

--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;

Gardie

This seems to be the better solution for me than Eric ones. However INSERT INTO Contact (Id, Name) SELECT 3, 'Bob' WHERE changes() = 0; should also work. – Occipital

Answer 10

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

Regen

I think that is not a good idea because you need to do twice request to database engine. – Strohben

Answer 11

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";

Scope

Answer 12

If someone wants to read my solution for SQLite in Cordova, I got this generic js method thanks to @david answer above.

function    addOrUpdateRecords(tableName, values, callback) {
get_columnNames(tableName, function (data) {
    var columnNames = data;
    myDb.transaction(function (transaction) {
        var query_update = "";
        var query_insert = "";
        var update_string = "UPDATE " + tableName + " SET ";
        var insert_string = "INSERT INTO " + tableName + " SELECT ";
        myDb.transaction(function (transaction) {
            // Data from the array [[data1, ... datan],[()],[()]...]:
            $.each(values, function (index1, value1) {
                var sel_str = "";
                var upd_str = "";
                var remoteid = "";
                $.each(value1, function (index2, value2) {
                    if (index2 == 0) remoteid = value2;
                    upd_str = upd_str + columnNames[index2] + "='" + value2 + "', ";
                    sel_str = sel_str + "'" + value2 + "', ";
                });
                sel_str = sel_str.substr(0, sel_str.length - 2);
                sel_str = sel_str + " WHERE NOT EXISTS(SELECT changes() AS change FROM "+tableName+" WHERE change <> 0);";
                upd_str = upd_str.substr(0, upd_str.length - 2);
                upd_str = upd_str + " WHERE remoteid = '" + remoteid + "';";                    
                query_update = update_string + upd_str;
                query_insert = insert_string + sel_str;  
                // Start transaction:
                transaction.executeSql(query_update);
                transaction.executeSql(query_insert);                    
            });
        }, function (error) {
            callback("Error: " + error);
        }, function () {
            callback("Success");
        });
    });
});
}

So, first pick up the column names with this function:

function get_columnNames(tableName, callback) {
myDb.transaction(function (transaction) {
    var query_exec = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name ='" + tableName + "'";
    transaction.executeSql(query_exec, [], function (tx, results) {
        var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(','); ///// RegEx
        var columnNames = [];
        for (i in columnParts) {
            if (typeof columnParts[i] === 'string')
                columnNames.push(columnParts[i].split(" ")[0]);
        };
        callback(columnNames);
    });
});
}

Then build the transactions programmatically.

"Values" is an array you should build before and it represents the rows you want to insert or update into the table.

"remoteid" is the id I used as a reference, since I'm syncing with my remote server.

For the use of the SQLite Cordova plugin, please refer to the official link

Tertian

Answer 13

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

Teletype

I don't think this works, it will wipe out the columns missing from the insert statement – Julio

@Mosor: -1 from me, sorry. This is the same as issuing a REPLACE statement. – Vittoria

-1 because this does a delete and then an insert if the primary key already exists. – Amu

Answer 14

This method remixes a few of the other methods from answer in for this question and incorporates the use of CTE (Common Table Expressions). I will introduce the query then explain why I did what I did.

I would like to change the last name for employee 300 to DAVIS if there is an employee 300. Otherwise, I will add a new employee.

Table Name: employees Columns: id, first_name, last_name

The query is:

INSERT OR REPLACE INTO employees (employee_id, first_name, last_name)
WITH registered_employees AS ( --CTE for checking if the row exists or not
    SELECT --this is needed to ensure that the null row comes second
        *
    FROM (
        SELECT --an existing row
            *
        FROM
            employees
        WHERE
            employee_id = '300'

        UNION

        SELECT --a dummy row if the original cannot be found
            NULL AS employee_id,
            NULL AS first_name,
            NULL AS last_name
    )
    ORDER BY
        employee_id IS NULL --we want nulls to be last
    LIMIT 1 --we only want one row from this statement
)
SELECT --this is where you provide defaults for what you would like to insert
    registered_employees.employee_id, --if this is null the SQLite default will be used
    COALESCE(registered_employees.first_name, 'SALLY'),
    'DAVIS'
FROM
    registered_employees
;

Basically, I used the CTE to reduce the number of times the select statement has to be used to determine default values. Since this is a CTE, we just select the columns we want from the table and the INSERT statement uses this.

Now you can decide what defaults you want to use by replacing the nulls, in the COALESCE function with what the values should be.

Quintilla

Answer 15

Following Aristotle Pagaltzis and the idea of COALESCE from Eric B’s answer, here it is an upsert option to update only few columns or insert full row if it does not exist.

In this case, imagine that title and content should be updated, keeping the other old values when existing and inserting supplied ones when name not found:

NOTE id is forced to be NULL when INSERT as it is supposed to be autoincrement. If it is just a generated primary key then COALESCE can also be used (see Aristotle Pagaltzis comment).

WITH new (id, name, title, content, author)
     AS ( VALUES(100, 'about', 'About this site', 'Whatever new content here', 42) )
INSERT OR REPLACE INTO page (id, name, title, content, author)
SELECT
     old.id, COALESCE(old.name, new.name),
     new.title, new.content,
     COALESCE(old.author, new.author)
FROM new LEFT JOIN page AS old ON new.name = old.name;

So the general rule would be, if you want to keep old values, use COALESCE, when you want to update values, use new.fieldname

Victoria

COALESCE(old.id, new.id) is definitely wrong with an autoincrementing key. And while “keep most of the row unchanged, except where values are missing” sounds like a use case someone might in fact have, I don’t think that’s what people are looking for when they’re looking for how to do an UPSERT. – Fluid

@AristotlePagaltzis apologise if I'm wrong, I'm not using autoincrements. What I'm looking for with this query is to update only few cols or insert full row with supplied values in case it does not exist. I've been playing with your query and I couldn't achieve it when inserting: the columns selected from old table where assigned to NULL, not to the values supplied in new. This is the reason to use COALESCE. I'm not an expert in sqlite, I've been testing this query and seems to work for the case, I would much thank you if you could point me to the solution with autoincrements – Victoria

In case of an autoincrementing key, you want to insert NULL as the key, because that tells SQLite to instead insert the next available value. – Fluid

I’m not saying you shouldn’t be doing what you are doing (if you need it then you need it), just that it’s not really an answer to the question here. UPSERT generally means you have a row that you want stored in the table and just don’t know whether you already have a matching row to put the values into or need to insert them as a new row. Your use case is that if you already have a matching row, you want to ignore most values from the new row. That’s fine, it’s just not the question that was asked. – Fluid

Answer 16

If you don't mind doing this in two operations.

Steps:

1) Add new items with "INSERT OR IGNORE"

2) Update existing items with "UPDATE"

The input to both steps is the same collection of new or update-able items. Works fine with existing items that need no changes. They will be updated, but with the same data and therefore net result is no changes.

Sure, slower, etc. Inefficient. Yep.

Easy to write the sql and maintain and understand it? Definitely.

It's a trade-off to consider. Works great for small upserts. Works great for those that don't mind sacrificing efficiency for code maintainability.

Edema

Note to everyone: INSERT OR REPLACE is NOT what this post is about. INSERT OR REPLACE will create a NEW row in your table, with a new ID. That isn't an UPDATE. – Edema

Answer 17

Complete example of upserting using WHERE to select the newer dated record.

-- https://www.db-fiddle.com/f/7jyj4n76MZHLLk2yszB6XD/22
 
DROP TABLE IF EXISTS db;

CREATE TABLE db
(
 id PRIMARY KEY,
 updated_at,
 other
);

-- initial INSERT
INSERT INTO db (id,updated_at,other) VALUES(1,1,1);

SELECT * FROM db;

-- INSERT without WHERE
INSERT INTO db (id,updated_at,other) VALUES(1,2,2)
ON CONFLICT(id) DO UPDATE SET updated_at=excluded.updated_at;

SELECT * FROM db;

-- WHERE is FALSE
INSERT INTO db (id,updated_at,other) VALUES(1,2,3)
ON CONFLICT(id) DO UPDATE SET updated_at=excluded.updated_at, other=excluded.other
WHERE excluded.updated_at > updated_at;

SELECT * FROM db;

-- ok to SET a PRIMARY KEY. WHERE is TRUE
INSERT INTO db (id,updated_at,other) VALUES(1,3,4)
ON CONFLICT(id) DO UPDATE SET id=excluded.id, updated_at=excluded.updated_at, other=excluded.other
WHERE excluded.updated_at > updated_at;

SELECT * FROM db;

Statvolt

Answer 18

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!

Bessiebessy

-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. – Haemolysin

Answer 19

SELECT COUNT(*) FROM table1 WHERE id = 1;

if COUNT(*) = 0

INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3);

else if COUNT(*) > 0

UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;

Coupon

This is way too complicated, SQL can handle this just fine in one query – Palmy