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

This is defined in SQL3 as MERGE.

Answer 2

Use an UPSERT pair of commands:

update test_table inner join test_table lookup 
on test_table.id = lookup.id
and lookup.name = 'bart'
set test_table.colA = ....

and

insert into test_table
select 1,'xxx', 999
from dual where exists <...>

Answer 3

One way of doing this is to simply execute an insert and update command, ignoring the error on the first if there's already a record with that key:

try:
    insert into test_table (id, name, weight) values(1,'pax',0)
catch (e):
    pass
update test_table set weight = weight * 1.1 where id = 1

If you want the initial weight of a created entry to be (for example) 72, use this as the first statement:

insert into test_table (id, name, weight) values(1,'pax',72/1.1)

Answer 4

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.