Menu
I've never seen the syntax INSERT OR REPLACE INTO names (id, name) VALUES (1, "John") used in SQL before, and I was wondering why it's better than UPDATE names SET name = "John" WHERE id = 1. Is there any good reason to use one over the other. Is this syntax specific to SQLite?
709
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.
The data modification clauses in SQLite are INSERT, UPDATE, and DELETE statements. It is used for inserting new rows, updating existing values, or deleting rows from the database.
The INSERT OR IGNORE INTO statement ignores the error message. The SELECT statement shows that the last two statements did not modify the fourth row. Since SQLite version 3.7. 11 it is possible to insert multiple rows using one INSERT statement.
Returns the current database page size, in bytes.
UPDATE will not do anything if the row does not exist.
Where as the INSERT OR REPLACE would insert if the row does not exist, or replace the values if it does.
84
I'm currently working on such a statement and figured out another fact to notice: INSERT OR REPLACE will replace any values not supplied in the statement. For instance if your table contains a column "lastname" which you didn't supply a value for, INSERT OR REPLACE will nullify the "lastname" if possible (constraints allow it) or fail.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
