On 2/25/2013 5:54 PM, anydacdev anydacdev 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)
If x.key happens to be unique (through PRIMARY KEY or UNIQUE constraint,
or a UNIQUE index), then you can do INSERT OR REPLACE
(http://sqlite.org/lang_conflict.html)
insert or replace into x(name, key)
select name, key from y;
If x has other fields besides name and key, and you want to preserve
them on update path, then it becomes a bit trickier:
insert or replace into x(name, key, otherField)
select name, key, otherField from y left join x on (y.key = x.key);
If x.key is not unique, then there's nothing better in SQLite than
running INSERT and UPDATE statements separately.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
