Igor Tandetnik wrote: > Dominique Pellé <dominique.pe...@gmail.com> wrote: >> For example, given 2 tables t1 and t2, both with 2 columns as follows... >> >> Table t1: >> >> ID name >> -- ---- >> 1 aaaa >> 2 NULL >> 3 NULL >> 4 bbbb >> (~1 million records) >> >> Table t2: >> >> ID name >> -- ---- >> 2 XXXX >> 4 YYYY >> (~500,000 records) >> >> ... I would like to update the 'name' column in records of table t1, so that >> they are the same as the 'name' column in table t2 when the ID columns >> match. > > update t1 set name = (select name from t2 where t1.ID=t2.ID) > where ID in (select ID from t2); > > Or, if t1.ID is a primary key or otherwise has a unique constraint: > > insert or replace into t1(ID, name) > select ID, name from t2;
Thanks Igor. That helped. I need to brush up my SQL. I'm still curious: what if I need to update several columns? Using almost the same example as in previous mail, I'd like to update columns l_nm and r_nm in table t1 using information in table t2 as depicted below: Table t1: ID_PK l_nm r_nm -- ---- ---- 1 aaaa NULL 2 NULL NULL 3 NULL NULL 4 bbbb NULL (~1 million records) Table t2: ID_FK l_nm r_nm -- ---- ---- 2 XXXX AAAA 4 YYYY BBBB (~500,000 records) Table t1 (after update): ID_PK l_nm r_nm -- ---- 1 aaaa NULL 2 XXXX AAAA <- l_nm & r_nm column updated. 3 NULL NULL 4 YYYY BBBB <- l_nm & r_nm column updated. (~1 million records) Using information in previous reply, I can do it with 2 UPDATE queries as follows (but I suspect that there is a better solution). UPDATE t1 SET l_nm = (SELECT l_nm FROM t2 WHERE t1.ID_PK = t2.ID_FK) WHERE ID_PK IN (SELECT ID_FK FROM t2); UPDATE t1 SET r_nm = (SELECT r_nm FROM t2 WHERE t1.ID_PK = t2.ID_FK) WHERE ID_PK IN (SELECT ID_FK FROM t2); I found information about updating multiple columns at... http://it.toolbox.com/wiki/index.php/How_do_I_update_multiple_fields_with_single_update_statement_using_subqueries%3F ... but SQLite does not accept the kind of queries in above page. Regards Dominique _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
