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

Question

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 way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction, sometimes separate.

The SQL must run unmodified on SQLite, PostgreSQL and MySQL, if possible.

A search yielded several ideas which either suffer from concurrency issues, or are specific to a database:

Try to INSERT a new row, and UPDATE if there was an error. Unfortunately, the error on INSERT aborts the current transaction.
UPDATE the row, and if no rows were modified, INSERT a new row.
MySQL has an ON DUPLICATE KEY UPDATE clause.

EDIT: Thanks for all the great replies. It looks like Paul is right, and there's not a single, portable way of doing this. That's quite surprising to me, as it sounds like a very basic operation.

You're not going to find a single solution that works for all these RDBMS. Sorry. — Paul Tomblin
– Paul Tomblin, Commented Mar 27, 2009 at 17:22
possible duplicate of SQLite - UPSERT *not* INSERT or REPLACE — PearsonArtPhoto
– PearsonArtPhoto, Commented Nov 12, 2012 at 23:28
possible duplicate of Solutions for INSERT OR UPDATE on SQL Server — Jonathan Leffler
– Jonathan Leffler, Commented Nov 12, 2012 at 23:30

andygeers · Accepted Answer · 2025-06-10 09:18:18Z

153

MySQL (and subsequently SQLite) also support the REPLACE INTO syntax:

REPLACE INTO my_table (pk_id, col1) VALUES (5, '123');

This automatically identifies the primary key and finds a matching row to update, inserting a new one if none is found. But as pointed out in the comments below, you need to make sure you supply values for all columns because even if the row already exists it will replace it with the values you supply (don't assume the new row will have the same primary key value as the old row either).

Documentation: https://dev.mysql.com/doc/refman/8.0/en/replace.html

edited Jun 10 at 9:18

answered Mar 27, 2009 at 17:15

andygeers

7,0869 gold badges53 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Evan Over a year ago

Actually, to be specific, MySQL's REPLACE always does an insert, but it will delete the row if it already exists, first. dev.mysql.com/doc/refman/4.1/en/replace.html

Zoredache Over a year ago

It is important to understand that it is an insert+delete and never and update. The consequence of this, is that you will always want to make sure when you do a replace, you should always include data for all the fields.

Agi Hammerthief Over a year ago

@Zoredache Technically, it's a 'delete, then insert', as a(n) 'insert + delete' is effectively the same as a delete but that's splitting hairs.

Tim Strijdhorst Over a year ago

@Agihammerthief there is a very real difference namely that the newly inserted row will NOT have the same primary key as the row that has been deleted. With ON DUPLICATE it will be the same primary key (unless you specifically change it).

Kyle Cronin · Accepted Answer · 2009-03-27 17:19:10Z

SQLite supports replacing a row if it already exists:

INSERT OR REPLACE INTO [...blah...]

You can shorten this to

REPLACE INTO [...blah...]

This shortcut was added to be compatible with the MySQL REPLACE INTO expression.

It requires that you defined a PRAMARY KEY in your values.

jmoz · Accepted Answer · 2009-03-27 17:45:40Z

I would do something like the following:

INSERT INTO cache VALUES (key, generation) ON DUPLICATE KEY UPDATE (key = key, generation = generation + 1);

Setting the generation value to 0 in code or in the sql but the using the ON DUP... to increment the value. I think that's the syntax anyway.

This answer should honestly be the selected answer. It's a non-destructive edit if you aren't submitting all fields to an entry.

Fire Crow · Accepted Answer · 2013-07-23 19:27:25Z

the ON DUPLICATE KEY UPDATE clause is the best solution because: REPLACE does a DELETE followed by an INSERT so for an ever so slight period the record is removed creating the ever so slight possibility that a query could come back having skipped that if the page was viewed during the REPLACE query.

I prefer INSERT ... ON DUPLICATE UPDATE ... for that reason.

jmoz's solution is the best: though I prefer the SET syntax to the parentheses

INSERT INTO cache SET key = 'key', generation = 'generation' ON DUPLICATE KEY UPDATE key = 'key', generation = (generation + 1) ;

REPLACE is atomic, so there's no period where the row doesn't exist.

Community · Accepted Answer · 2017-05-23 11:46:46Z

I don't know that you are going to find a platform-neutral solution.

This is commonly called an "UPSERT".

See some related discussions:

user80168 · Accepted Answer · 2009-03-27 17:41:43Z

In PostgreSQL there is no merge command, and actually writing it is not trivial - there are actually strange edge cases that make the task "interesting".

The best (as in: working in the most possible conditions) approach, is to use function - such as one shown in manual (merge_db).

If you don't want to use function, you can usually get away with:

updated = db.execute(UPDATE ... RETURNING 1) if (!updated) db.execute(INSERT...)

Just remember that it is not fault proof and it will fail eventually.

Jonathan Leffler · Accepted Answer · 2009-03-27 17:27:11Z

4

Standard SQL provides the MERGE statement for this task. Not all DBMS support the MERGE statement.

answered Mar 27, 2009 at 17:27

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Comments

Shea · Accepted Answer · 2009-03-27 17:11:07Z

If you don't have a common way to atomically update or insert (e.g., via a transaction) then you can fallback to another locking scheme. A 0-byte file, system mutex, named pipe, etc...

Michael Todd · Accepted Answer · 2009-03-27 17:12:25Z

0

Could you use an insert trigger? If it fails, do an update.

answered Mar 27, 2009 at 17:12

Michael Todd

17.2k4 gold badges52 silver badges70 bronze badges

1 Comment

user80168 Over a year ago

Trigger (at least in PostgreSQL) is running when the command worked. i.e. you can't have trigger that runs when base command failed.

Seamus Abshere · Accepted Answer · 2013-11-05 15:06:17Z

If you're OK with using a library that writes the SQL for you, then you can use Upsert (currently Ruby and Python only):

Pet.upsert({:name => 'Jerry'}, :breed => 'beagle') Pet.upsert({:name => 'Jerry'}, :color => 'brown')

That works across MySQL, Postgres, and SQLite3.

It writes a stored procedure or user-defined function (UDF) in MySQL and Postgres. It uses INSERT OR REPLACE in SQLite3.

Collectives™ on Stack Overflow

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

10 Answers 10

4 Comments

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

4 Comments

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Linked

Related