So what are you going to do? we need to get your plans pinned down. On Jun 17, 2009, at 11:46 AM, James Gregurich wrote:
> > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
