ANSI SQL question - how to insert or update a record if it already exists?

Question

Although I am using mySQL (for now), I dont want any DB specific SQL.

I am trying to insert a record if it doesn't exist, and update a field if it does exist. I want to use ANSI SQL.

The table looks something like this:

create table test_table (id int, name varchar(16), weight double) ;

//test data 
insert into test_table (id, name, weight) values(1,'homer', 900);
insert into test_table (id, name, weight) values(2,'marge', 85);
insert into test_table (id, name, weight) values(3,'bart', 25);
insert into test_table (id, name, weight) values(4,'lisa', 15);

If the record exists, I want to update the weight (increase by say 10)

Answer 1

For a long time this operation required two separate commands plus some framework to handle it. Hence the name UPSERT (UPdate or inSERT). But more recent versions of some flavours of DBMS support more elegant solutions.

The ANSI standard defines a MERGE syntax. This has been supported in Oracle since version 9i and in MS SQL Server since 2005. MERGE statements can be somewhat verbose.

merge into t23
using t42
on t42.id = t23.id
when matched then
    update
    set     t23.col1 = t42.col1
when not matched then
    insert  (id, col1)
    values  (t42.id, t42.col1)
/

I think the MERGE statement was primarily envisaged as a data migration tool, so its syntax demands that we select data from a table, in the USING clause. we can get around this limitation by selecting literals and pseudo-columns from a row-generating device (such as dual in Oracle).

MySQL has a sightly different syntax, INSERT ... ON DUPLICATE KEY UPDATE.

Answer 2

An approach compliant with older SQL standards and therefore compatible with a broader range of DBMS (as of now SQLite, for example, does not support MERGE) is to use a technique involving a mutex table:

CREATE TABLE mutex (i INT);
INSERT INTO mutex VALUES (0);

That enables the emulation of an INSERT IF NOT EXISTS statement:

INSERT INTO test_table (id, name, weight)
  SELECT 1, 'homer', 900
  FROM mutex LEFT JOIN test_table
  ON id = 1
  WHERE i = 0 AND id IS NULL;

In the case of the OP's question that would be followed by a simple UPDATE:

UPDATE test_table SET weight = weight + 10 WHERE id = 1;