SQLite "INSERT OR REPLACE INTO" vs. "UPDATE ... WHERE"

sqlite upsert
nevan king picture nevan king · Feb 12, 2010 · Viewed 123k times · Source

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?

Answer

Adriaan Stander picture Adriaan Stander · Feb 12, 2010

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.

Related questions

INSERT IF NOT EXISTS ELSE UPDATE?

I've found a few "would be" solutions for the classic "How do I insert a new record or update one if it already exists" but I cannot get any of them to work in SQLite. I have a table defined …

Read More
SQLite - UPSERT *not* INSERT or REPLACE

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 …

Read More
How do I UPDATE a row in a table or INSERT it if it doesn't exist?

I have the following table of counters: CREATE TABLE cache ( key text PRIMARY KEY, generation int ); I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a …

Read More