Thanks Igor. Yes, my situation resembles the second case. The SQLite statement is (surprisingly) compact :).
I am struggling with SQLite's support for Oracle's DUAL table. The updated statement, now including Oracle's DUAL looks like. MERGE INTO x TGT USING (SELECT 'A_NAME' as name, 'A_KEY' as key FROM DUAL) SRC ON (TGT.key = SRC.key) WHEN MATCHED THEN UPDATE SET TGT.NAME = NAME WHEN NOT MATCHED THEN INSERT (TGT.NAME) VALUES (SRC.NAME) It would be great to know if SQLite has support for this. Otherwise would are my options? Thanks. 2013/2/26 anydacdev anydacdev <anydac...@gmail.com> > > > ---------- Forwarded message ---------- > From: anydacdev anydacdev <anydac...@gmail.com> > Date: 2013/2/26 > Subject: Fwd: [sqlite] SQLite equivalent to Oracle's MERGE INTO > To: ronanvanr...@ymail.com > > > > > ---------- Forwarded message ---------- > From: Igor Tandetnik <i...@tandetnik.org> > Date: 2013/2/26 > Subject: Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO > To: sqlite-users@sqlite.org > > > 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<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<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
