What is the most efficient way to do check if a value exist then Update or Insert in Sql

Question

Hi

I need to update date a value in table if it does not exist then it must be inserted

What is the best way to does this in MySql

Currently I am using

   SELECT Id INTO LId  FROM ATABLE WHERE ID = FID;
   IF LId IS NULL THEN
     INSERT INTO ATABLE(abc) Values (2)
   ELSE
     UPDATE ATABLE Set Abc = 2 Where Id = LId
   END IF;

But this hits the Database 3 times

Is there a better way of doing this ?

Answer 1

Here if what you need.

I use INSERT ... ON DUPLICATE KEY UPDATE.

Answer 2

INSERT ... ON DUPLICATE KEY UPDATE

That's what you're looking for :)

See here for more details.

Answer 3

For MySql Upsert, you might want to check out this blog post. It highlights a few methods including doing an update and then an insert with a left-join:

update t1 as l
    inner join t2 as r on l.a = r.d
    set l.b = r.e, l.c = r.f;

insert into t1 (a, b, c)
    select l.d, l.e, l.f
    from t2 as l
        left outer join t1 as r on l.d = r.a
    where r.a is null;

A single statement upsert is possible using ON DUPLICATE KEY UPDATE:

insert into t1(a, b, c)
    select d, e, f from t2
    on duplicate key update b = e, c = f;