On Sunday, January 21, 2007, 1:17:09 PM, RB Smissaert<sqlite-users@sqlite.org> wrote: > Need to do an UPDATE on one table, based on values in another table where > this other table is joined to the first table on rowid of table 1 = column > value of table 2, so:
> UPDATE > table1 > SET col2 = > (SELECT > t2.col2 > FROM > table2 t2 > WHERE > ROWID = t2.col1) > It runs fine, but all the update values are the same as it will take the > rowid of table2, not table1. > The problem is that I can set an alias on table2, but not table1 as that > will cause an invalid query. > I am sure there must be a simple solution for this and thanks for anybody > telling me. > RBS > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- Shouldn't it be ".... WHERE t1.rowid = t2.col1" / notice the added t1. before rowid / to make this work as you expected? It implicitly takes the rowid in the subquery from table 2 because you select from table 2 in the subquery. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
