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

[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 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.  


As SQLite can only have one writer at the time, perhaps a 'begin immediate 
transaction;' will deal with that.

Regarding the 'update' part, I'm not sure how unwieldy  this will grow when 
introducing more columns, and more feature, such as the ability to update only 
rows which have effective changes.


So, all in all, one may end up with the following:

--8<--

begin immediate transaction;

-- assume 'source' is populated somehow, somewhere, before hand… 

update  target
set value1 = ( select value1 from source where source.key = target.key ),
value2 = ( select value2 from source where source.key = target.key ),
value3 = ( select value3 from source where source.key = target.key ),
value4 = ( select value4 from source where source.key = target.key )
where   exists
(
  select  1
  fromsource
  where   source.key = target.key
  and (
coalesce( source.value1, '-' ) != coalesce( target.value1, 
, '-' ) or
coalesce( source.value2, '-' ) != coalesce( target.value2, 
, '-' ) or
coalesce( source.value3, '-' ) != coalesce( target.value3, 
, '-' )  or
coalesce( source.value4, '-' ) != coalesce( target.value4, 
, '-' ) 
  )
);

insert  
intotarget
(
  key,
  value1,
  value2,
  value3,
  value4
)
select  key,
value1,
value2,
value3,
value4
fromsource

where   not exists
(
  select  1
  fromtarget
  where   target.key = source.key
);

commit transaction;

-->8--

Quite a mouthful. Not to even mention all these scalar queries and multiple 
passes over both source and target.


Compare that to an hypothetical  merge statement:

merge
intotarget
using 
(
  select  key,
  value1,
  value2,
  value3,
  value4
  fromsource
) source
on
(
  target.key = source.key
)
whenmatched then update
set target.value1 = source.value1,
target.value2 = source.value2,
target.value3 = source.value3,
target.value4 = source.value4
where   coalesce( target.value1, '-' ) != coalesce( source.value1, , '-' ) 
or  coalesce( target.value2, '-' ) != coalesce( source.value2, , '-' ) 
or  coalesce( target.value3, '-' ) != coalesce( source.value3, , '-' )  
or  coalesce( target.value4, '-' ) != coalesce( source.value4, , '-' )
whennot matched then insert
(
  key,
  value1,
  value2,
  value3,
  value4
)
values  (
  source.key,
  source.value1,
  source.value2,
  source.value3,
  source.value4
);

Another major benefit of merge is that the 'using' clause can be any query. No 
need for a preexisting source as for the update/insert scenario above.


Alternatively, this could be all turned inside out, and dealt with 
programmatically. Pseudo code:


for row in
(
  selectsource.key as skey,
source.value1 as svalue1,
source.value2 as svalue2,
source.value3 as svalue3,
source.value4 as svalue4,
target.key as tkey,
target.value1 as tvalue1,
target.value2 as tvalue2,
target.value3 as tvalue3,
target.value4 as tvalue4
  from  source

  left join target
  ontarget.key = source.key
)
loop
  if row.target is null then
insert
intotarget
(
  key,
  value1,
  value2,
  value3,
  value4
)
values  (
  row.skey,
  row.svalue1,
  row.svalue2,
  row.svalue3,
  row.svalue4
);
  elseif coalesce( row.tvalue1 ) != coalesce( row.svalue1, , '-' ) 
  or coalesce( row.tvalue2, '-' ) != 

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

[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 way to implement the missing
OR IGNORE (both in SQLite3 apps and in general), and combined with the
subsequent INSERT this is the canonical way to implement INSERT OR
UPDATE.

> 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.

Who might change it?  We're in an exclusive transaction at this point
and SQLite3 allows only one writer at a time.  Another thread with the
same db handle?

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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) 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

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

[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 name, 'A_KEY' as key FROM DUAL) SRC
>
>
> As far as I can tell, you are using DUAL as a table with no data, just

Igor, a little correction here: it's not table with no data, it's
table with exactly one row. If it was table with no data then SELECT
.. FROM DUAL would have returned nothing. ;-)

But anyway you suggested the correct replacement for that in SQLite.


Pavel

> something to put into a FROM clause to keep the parser happy. In this case,
> you would be glad to know that SQLite doesn't require FROM clause at all -
> you can simply write
>
>
> SELECT 'A_NAME' as name, 'A_KEY' as key
>
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 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

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

[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 DUAL as a table with no data, just 
something to put into a FROM clause to keep the parser happy. In this 
case, you would be glad to know that SQLite doesn't require FROM clause 
at all - you can simply write


SELECT 'A_NAME' as name, 'A_KEY' as key

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[anydacdev anydacdev]

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

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

[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  'insert or ignore' + 'update'. But that's 
about it. Rather limited and cumbersome. Sigh…

If there was just *one* statement I wish SQLite supported, MERGE would be it. 

N.B. 

Be wary of 'insert or replace'  as it's really the equivalent of 'delete' + 
'insert'… usually not what one wants…

http://www.sqlite.org/lang_conflict.html

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 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

[sqlite] SQLite equivalent to Oracle's MERGE INTO

[anydacdev anydacdev]

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)

Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users