You could also try this. I imagine it has to do much less internal querying to get the job done. This does assume you have a single column PK, beyond the rowid, that would not change when the other columns changes.
INSERT OR REPLACE INTO T1 SELECT * FROM T2; DELETE FROM T1 WHERE pk NOT IN (SELECT pk FROM T2); On 5/9/06, Unit 5 <[EMAIL PROTECTED]> wrote:
--- Jim Dodgen <[EMAIL PROTECTED]> wrote: > this should work. > > UPDATE T1 > SET T1.colA = (select T2.colA from T2 where T1.colC > = T2.colC), > T1.colB = (select T2.colB from T2 where T1.colC > = T2.colC) > WHERE EXISTS (select * from T2 where T1.colC = > T2.colC); > Jim, Thanks for your answer. Indeed, it works great. I checked the statement with EXPLAIN but as I am new to SQLite, did not make much sense of the plan. However, it seems like the same sub-select gets executed repeatedly, once for each updated column and once at the end. Does this degrade performance or does the engine optimize these separate statements into one? I ask because the file I need to run this update statement against has several million records. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
