oops. sorry for errant message, folks. I had the wrong email selected when I hit the button and didn't pay attention to what I was doing.
On Jun 17, 2009, at 3:19 PM, James Gregurich wrote: > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
