SQLite INSERT OR REPLACE INTO vs UPDATE WHERE

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sqlite] SQLite "INSERT OR REPLACE INTO" vs. "UPDATE ... WHERE"

Examples related to sqlite

Examples related to upsert