Atomic Upsert MySQL

Question

What is the best method to atomically update a row if exists, else insert a new row into an table?

e.g. say we have this example below.

CREATE TABLE Test(
id integer,
val varchar(255)
)

DELIMITER $$

DROP PROCEDURE IF EXISTS `TestUpsert` $$
CREATE PROCEDURE `TestUpsert` (in value1 varchar(255), in id1 int)
BEGIN
    IF EXISTS(Select 1 from Test where id=id1) THEN
        UPDATE Test set val  = value1 where id = id1;
    ELSE
        insert into Test(id, val) VALUES (id1, value1);
    END IF;

END $$

DELIMITER ;

What changes can we make to TestUpsert to make it atomic?

This is clearly MySQL, so I removed the T-SQL tag. – Gordon Linoff Mar 14 '17 at 01:19 — Gordon Linoff, Mar 14 '17 at 01:19

Gordon Linoff · Answer 1 · 2017-03-14T02:31:09.587

1

Use on duplicate key update:

CREATE PROCEDURE `TestUpsert` (
    in in_value varchar(255),
    in in_id int
)
BEGIN
    insert into test(id, val)
        values (in_id, in_val)
        on duplicate key update val = in_val;
END $$

I should add that for this to work, test.id has to be declared as the primary key or unique.

edited Mar 14 '17 at 02:31

answered Mar 14 '17 at 01:20

Gordon Linoff

1,242,037
58
646
786

What if my where statement is not for a pk – Max Mar 14 '17 at 01:23
@Max . . . It should be unique; otherwise, how to do you know which row to update? – Gordon Linoff Mar 14 '17 at 02:31
e.g. We could have compound keys - e.g.CREATE TABLE Test( id integer, id2 integer, val varchar(255) ) Then we would be updating val on id and id2. – Max Mar 14 '17 at 02:37
@Max . . . This identifies a unique row using available primary keys and unique indexes. The number of keys doesn't matter. – Gordon Linoff Mar 14 '17 at 12:54

score 0 · Answer 2 · edited May 23 '17 at 12:17

0

I recommend you read about INSERT ... ON DUPLICATE KEY UPDATE.

See my lengthy answer about this statement here: https://stackoverflow.com/a/548570/20860

edited May 23 '17 at 12:17

Community

1
1

answered Mar 14 '17 at 01:21

Bill Karwin

538,548
86
673
828

Atomic Upsert MySQL

2 Answers2