On Feb 27, 2013, at 2:53 PM, James K. Lowden <jklow...@schemamania.org> wrote:
> 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.
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
from source
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
into target
(
key,
value1,
value2,
value3,
value4
)
select key,
value1,
value2,
value3,
value4
from source
where not exists
(
select 1
from target
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
into target
using
(
select key,
value1,
value2,
value3,
value4
from source
) source
on
(
target.key = source.key
)
when matched 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, , '-' )
when not 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
(
select source.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
on target.key = source.key
)
loop
if row.target is null then
insert
into target
(
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, '-' ) != coalesce( row.svalue1, , '-' )
or coalesce( row.tvalue3, '-' ) != coalesce( row.svalue1, , '-' )
or coalesce( row.tvalue4, '-' ) != coalesce( row.svalue1, , '-' ) then
update target
set value1 = row.svalue1,
value2 = row.svalue2,
value3 = row.svalue3,
value4 = row.svalue4
where key = row.skey;
end;
end loop;
Not that pleasing either. Sigh...
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
