On Mon, 25 Feb 2013 23:54:23 +0100
anydacdev anydacdev <anydac...@gmail.com> wrote:
> I was wondering what is SQLite's equivalent to:
>
> MERGE INTO x TGT
> USING (SELECT NAME, KEY FROM y) SRC
> ON (TGT.key = SRC.key)
> WHEN MATCHED THEN
> UPDATE SET TGT.NAME = NAME
> WHEN NOT MATCHED THEN
> INSERT (TGT.NAME) VALUES (SRC.NAME)
begin transaction;
update tgt
set name = (select name from src where tgt.key = src.key)
where exists (
select 1 from src
where src.key = tgt.key
);
-- check for error
insert into tgt (name)
select name from src
where not exists (
select 1 from tgt
where tgt.key = src.key
);
-- check for error
commit transaction;
It's only close, not equivalent, because MERGE is atomic: here src and
tgt could change between UPDATE and INSERT. That you'll have to deal
with using timestamps or some form of advisory locking.
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
