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

I've never seen the syntax INSERT OR REPLACE INTO names (id, name) VALUES (1, "John") used in SQL before, a开发者_运维百科nd 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?

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.

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 (if constraints allow it), or fail.

REPLACE INTO table(column_list) VALUES(value_list);

is a shorter form of

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

For REPLACE to execute correctly your table structure must have unique rows, whether a simple primary key or a unique index.

REPLACE deletes, then INSERTs the record and will cause an INSERT Trigger to execute if you have them setup. If you have a trigger on INSERT, you may encounter issues.

This is a work around.. not checked the speed..

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 a replace to occur without causing a trigger.

The insert or replace query would insert a new record if id=1 does not already exist.

The update query would only oudate id=1 if it aready exist, it would not create a new record if it didn't exist.