Unit 5 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
Unit,
You are correct, the subselects are executed multiple times. You can
avoid this by using several SQL statements to do the job inside a
transaction.
First build a temp table with the desired column values for the final
table by joining the two existing tables. Then delete all the records
from the existing table T1 and copy all the records from the temp table
back into T1. Finally, delete the update records from T2, and drop the
temp table.
begin transaction;
create temp table T3 as
select
case when T2.colC isnull then T1.colA else T2.colA end as colA,
case when T2.colC isnull then T1.colB else T2.colB end as colB,
T1.colC as colC
from T1 left join T2 on T1.colC = T2.colC;
delete from T1;
insert into T1 select * from T3;
delete from T2;
drop table T3;
commit transaction;
This may or may not be any faster depending upon the number of records
in T1 and T2. Try it out.
Both forms should be much faster if T1 and T2 have an index colC.
HTH
Dennis Cote
