75

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?

dreftymac
  • 31,404
  • 26
  • 119
  • 182
nevan king
  • 112,709
  • 45
  • 203
  • 241

4 Answers4

114

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.

nevan king
  • 112,709
  • 45
  • 203
  • 241
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • 7
    In this question http://stackoverflow.com/questions/690632/how-do-i-update-a-row-in-a-table-or-insert-it-if-it-doesnt-exist it is stated that replace into will always delete the row first while update doesn't – Display name Jan 02 '13 at 08:18
  • 5
    To explicitly state the implication of @Seppl's helpful comment: _always specify the complete set of values you want in the "updated" row_; since the old row gets deleted first, none of its values are preserved. – mklement0 May 16 '14 at 20:34
41

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.

mwfearnley
  • 3,303
  • 2
  • 34
  • 35
cybergen
  • 3,108
  • 1
  • 22
  • 30
  • 7
    +1; to put it differently: always specify the _complete set of values_ the "updated" row should have - no existing values are preserved; the reason for this behavior is that is in fact not an *update* operation for existing row, but a deletion, followed by a re-insertion. – mklement0 May 16 '14 at 20:39
26
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.

PodTech.io
  • 4,874
  • 41
  • 24
5

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.

Anonym
  • 3,098
  • 4
  • 31
  • 32
  • 2
    I don't think this is right. The sqlite documentation (as of 1st July 2010, version 3.6.23.1) says that `REPLACE is an alias for INSERT OR REPLACE`, and would hence have the same behaviour. In my experience this is the case.. – fostandy Jun 30 '10 at 16:36
  • I think it will do a delete->insert if it finds a conflict – StErMi Jan 06 '12 at 18:06
  • 2
    @fostandy REPLACE is indeed an alias for INSERT OR REPLACE, but the answer on which you're commenting is talking about UPDATE (because that's what the question was about). – hatfinch Feb 02 '12 at 22:27