I use this technique frequently and provided things are properly indexed it runs very fast.
Quoting Unit 5 <[EMAIL PROTECTED]>: > --- 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 >
