SQLite "INSERT OR REPLACE IN" and "UPDATE ... WHERE"

Question

I have never seen the INSERT OR REPLACE INTO names (id, name) VALUES (1, "John") syntax used in SQL before, and I was wondering why this is better than UPDATE names SET name = "John" WHERE id = 1 . Is there a good reason to use one over the other. Is this syntax specific to SQLite?

Answer 1

UPDATE will do nothing if the row does not exist.

If you insert INSERT or REPLACE if the row does not exist, or replace the values ​​if this happens.

Answer 2

I am currently working on such an expression and have found out one more fact: INSERT or REPLACE will replace any values ​​not specified in the instruction. For example, if your table contains a column "lastname" for which you did not specify a value, INSERT or REPLACE will hide the "last name", if possible (restrictions allow it) or a failure.

Answer 3

Insert or replace the request will insert a new record if id = 1 does not exist yet.

The update request will only oudate id = 1, if it exists, it will not create a new record if it does not exist.

Answer 4

 REPLACE INTO table(column_list) VALUES(value_list); 

- shorter form

 INSERT OR REPLACE INTO table(column_list) VALUES(value_list); 

For REPLACE to work properly, your table structure must have unique rows, be it a simple primary key or a unique index.

REPLACE deletes, then INSERT writes the record and triggers the INSERT trigger, if you have the setting. If you have an INSERT trigger, you may run into problems.

---

This is the work around. Speed ​​not verified.

 INSERT OR IGNORE INTO table (column_list) VALUES(value_list); 

followed by

 UPDATE table SET field=value,field2=value WHERE uniqueid='uniquevalue' 

This method allows replacement without triggering.