Dennis, question on an old post of yours below...
on that update statement, is the SQL optimizer smart enough to not rerun that select statement for each column in the update's set clause? Is it going to run a single select statement to get <value1>, <value2>, etc. or is it going to run one for each column in the update statement? -James > 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 > 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
