Petite Abeille wrote:
>
> How does one emulate a DML MERGE statement in SQLite [1]?
>
> INSERT OR REPLACE sounds promising but the REPLACE documentation under
> the ON CONFLICT clause seems to imply that in the case of a constraint
> violation the existing row will be deleted entirely and then replaced
> by a brand new row instead of being merely updated [2].
>
> Apologies if this is a FAQ, but my google-fu is eluding me on this one.
>
> Thanks in advance.
>
I haven't tested this so take it with a grain of salt, but I think this
should do the same thing as the merge statement.
Given two tables, table1 and table2.
merge into table1 using table2 on <condition>
when matched then update
set <column1> = <value1>,
<column2> = <value2> ...
when not matched then insert <columm1>, <column2> ...
values (<value1>, <value2> ...)
Should be the same as the following series of SQL statements.
create temp table matches as
select t1.rowid as row1, t2.rowid as row2
from table1
join table2
where <condition>
insert into table1 (<column1>, <column2> ...)
select <value1>, <value2> ... from table2
where rowid not in (select row2 from matches);
update table1
set <column1> = (select <value1> from table2
where table2.rowid =
(select row2 from matches
where row1 = rowid)),
<column2> = (select <value2> from table2
where table2.rowid =
(select row2 from matches
where row1 = rowid))
...
where rowid in (select row1 from matches);
drop table matches;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
