Cross-table UPDATE in SQLITE3 Cross-table UPDATE in SQLITE3 sql sql

Cross-table UPDATE in SQLITE3


This works for sqlite:

UPDATE tbl1 SET col2 = (SELECT col2 FROM tbl2 WHERE tbl2.col1 = tbl1.col1)


Just to emphasize Geogory Higley's post:

I have had problems with UPDATE tbl1 SET col2 = (SELECT col2 FROM tbl2 WHERE tbl2.col1 = tbl1.col1) where it updates columns in tbl1 that do not exist in tbl2.

see cheetah post at http://sqlite.phxsoftware.com/forums/p/1708/7238.aspx which points to:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg27207.html

The code is:

insert or replace into foo (id, name, extra)select bar.id, bar.name, foo.extra  from bar   left join foo     on bar.id = foo.id;

and this seems to work correctly. There seem to be many posts at different sites that recommend the first approach so it is a bit confusing. I would suggest you test your output very carefully if you use this method which does seem faster and may work with matched tables.


I've discovered this can be done with INSERT OR REPLACE INTO. A little more verbose than T-SQL's equivalent, but just as handy.