What is the most efficient way to check if a value exists then update or insert into Sql
I need to update a date in a table, if it doesn't exist then it should be inserted
What is the best way to do this in MySql
I am currently 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 to do this?
+2
source to share
3 answers
For MySql Upsert, you can check this blog post . It highlights several methods, including performing an update and then pasting 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;
One statement can be set 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;
+3
source to share