Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

2013-02-27 Thread Petite Abeille
On Feb 27, 2013, at 2:53 PM, James K. Lowden wrote: > On Mon, 25 Feb 2013 23:54:23 +0100 > 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 SE

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

2013-02-27 Thread Nico Williams
On Wed, Feb 27, 2013 at 7:53 AM, James K. Lowden wrote: > 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 Yes, this is what I'd call the canonical w

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

2013-02-27 Thread James K. Lowden
On Mon, 25 Feb 2013 23:54:23 +0100 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) VAL

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

2013-02-26 Thread Pavel Ivanov
On Tue, Feb 26, 2013 at 2:29 PM, Igor Tandetnik wrote: > On 2/26/2013 5:13 PM, anydacdev anydacdev wrote: >> >> 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 na

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

2013-02-26 Thread Igor Tandetnik
On 2/26/2013 5:13 PM, anydacdev anydacdev wrote: 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 As far as I can tell, you are using DUA

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

2013-02-26 Thread anydacdev anydacdev
--- > From: Igor Tandetnik > 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: >>

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

2013-02-25 Thread Petite Abeille
On Feb 25, 2013, at 11:54 PM, anydacdev anydacdev wrote: > I was wondering what is SQLite's equivalent to: > > MERGE INTO x TGT There is none. Even though it's standard SQL (SQL:2003 or such), this is not supported by SQLite in any way, shape, or form. One could make do with a loop of 'inse

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

2013-02-25 Thread Igor Tandetnik
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 hap