UPSERT *not* INSERT or REPLACE

by

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.

enter image description here

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

More Related Contents:

  1. SQLite – UPSERT *not* INSERT or REPLACE
  2. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  3. Solutions for INSERT OR UPDATE on SQL Server
  4. Oracle: how to UPSERT (update or insert into a table?)
  5. SQLite DateTime comparison
  6. What are valid table names in SQLite?
  7. Deleting duplicate rows from sqlite database
  8. What does the SQL Standard say about usage of backtick(`)?
  9. Delete column from SQLite table
  10. Declare variable in SQLite and use it
  11. sqlite database default time value ‘now’
  12. How do I insert datetime value into a SQLite database?
  13. How to get a list of column names on Sqlite3 database?
  14. basic recursive query on sqlite3?
  15. Does DB2 have an “insert or update” statement?
  16. SQLite and custom order by
  17. SQLite auto-increment non-primary key field
  18. GROUP_CONCAT in SQLite
  19. How to do IF NOT EXISTS in SQLite
  20. Core Data Performance with Single Parent Entity
  21. Match only entire words with LIKE?
  22. What is causing Foreign Key Mismatch error?
  23. How to split comma-separated values?
  24. sqlite select with condition on date
  25. How to compare sqlite TIMESTAMP values
  26. How to resolve SQL0418N Error
  27. SQL – Inserting a row and returning primary key
  28. How to select the first N rows of each group?
  29. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  30. Copy table structure to new table in sqlite3

Leave a Comment