Great thanks. Now why didn't I think of that especially as I was messing around with ROWID earlier?
Cheers > update TABLE2 set z = @z > where rowid in ( > select t2.rowid > from TABLE1_2 t12, TABLE2 t2 > where t12.a = @a > and t12.b = @b > and t2.x = t12.x > and t2.y = t12.y >) > > > Pavel > > On Fri, Jun 25, 2010 at 9:04 AM, Matthew Jones > <matthew.jo...@hp.com<mailto:matthew.jo...@hp.com>> wrote: > > I've seen various posts about who to get around the lack of UPDATE with > > a JOIN but they all seem to refer to tables joined on a single column. I > > need to do something very similar but with two-column primary key. E.g. > > > > sqlite> create table TABLE1 (a int, b int, primary key(a, b)); > > sqlite> create table TABLE2 (x int, y int, z int, primary key(x, y)); > > sqlite> create table TABLE1_2 (a int, b int, x int, y int, primary > > key(a, b, x, y)); > > > > So I have a many to many relationship between table 1 and 2. I now want > > to update column z in TABLE2 for all entries that match a particular row > > in TABLE1. Obviously, TABLE1_2 can be queried to give me the keys of all > > the rows in TABLE2 that need to be updated but how do I actually do the > > update? > > > > Thanks _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
