INSERT IF NOT EXISTS ELSE UPDATE?

by

Have a look at http://sqlite.org/lang_conflict.html.

You want something like:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values
((select ID from Book where Name = "SearchName"), "SearchName", ...);

Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why there’s a subselect for the ID column: In the replacement case the statement would set it to NULL and then a fresh ID would be allocated.

This approach can also be used if you want to leave particular field values alone if the row in the replacement case but set the field to NULL in the insert case.

For example, assuming you want to leave Seen alone:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
   (select ID from Book where Name = "SearchName"),
   "SearchName",
    5,
    6,
    (select Seen from Book where Name = "SearchName"));

More Related Contents:

  1. SQLite “INSERT OR REPLACE INTO” vs. “UPDATE … WHERE”
  2. SQLite how to refer to column name by a query
  3. SQLite – UPSERT *not* INSERT or REPLACE
  4. Android SQLite database: slow insertion
  5. How do I unlock a SQLite database?
  6. “Insert if not exists” statement in SQLite
  7. How do I make an UPDATE while joining tables on SQLite?
  8. SQLite Concurrent Access
  9. System.Data.SQLite Close() not releasing database file
  10. Sqlite LIMIT / OFFSET query
  11. Set start value for AUTOINCREMENT in SQLite
  12. ALTER COLUMN in sqlite
  13. SQLite error ‘attempt to write a readonly database’ during insert?
  14. Efficient paging in SQLite with millions of records
  15. SQLite add Primary Key
  16. Encrypted cookies in Chrome
  17. SQLite UPSERT / UPDATE OR INSERT
  18. ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite
  19. How to get a list of column names
  20. sqlite – How to get INSERT OR IGNORE to work
  21. Creating stored procedure and SQLite?
  22. How Scalable is SQLite? [closed]
  23. change sqlite file size after “DELETE FROM table”
  24. Add ON DELETE CASCADE behavior to an sqlite3 table after it has been created
  25. How to set unique constraint over multiple columns when any one can be null?
  26. Will SQLite performance degrade if the database size is greater than 2 gigabytes?
  27. Python and SQLite: insert into table
  28. SQLite for large data sets?
  29. SQLite database supporting Unicode data
  30. SQLITE3 VACUUM, “database or disk is full”

Leave a Comment