Geek Answers Handbook

ANSI SQL Question - How to insert or update a record if it already exists?

Although I use mySQL (for now), I don't want any specific SQL code.

I am trying to insert a record if it does not exist and update the field if it exists. 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)
+7
sql
source share
5 answers

Over time, this operation took two separate teams plus some frameworks. Therefore, the name is UPSERT (UPDATE or inSERT). But more recent versions of some DBMS options support more elegant solutions.

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

 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 that the MERGE expression was primarily intended as a means of transferring data, so its syntax requires us to 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 (for example, double in Oracle).

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

+7
source share

An approach that complies with old SQL standards and, therefore, is compatible with a wider range of DBMSs (currently SQLite, for example, does not support MERGE ), consists in using a method that uses a mutex table :

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

This allows 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 an OP question, followed by a simple UPDATE:

 UPDATE test_table SET weight = weight + 10 WHERE id = 1;
+2
source share

This is defined in SQL3 as MERGE .

+1
source share

Use UPSERT a couple 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 <...>
+1
source share

One way to do this is to simply execute the insert and update command, ignoring the error on the first if there is already an entry with this 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 the created record to be (for example) 72, use this as the first statement:

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

More articles:


All Articles