Oops, for logical clarity, it should be: UPDATE dest SET dest.field = (SELECT src.field FROM src, dest WHERE src.dest_id = dest.id);
On Fri, Nov 19, 2010 at 11:20 AM, Audra Rudys <sphin...@gmail.com> wrote: > I figured out a way to do this... rewrote my query as a nested select: > UPDATE dest SET dest.field = (SELECT src.field FROM src, dest WHERE > src.dest_id = dest.id); > > Does SQLite support UPDATE statements with multiple tables or does one have > to nest select statements to achieve this? > > Thanks, > Audra > > > > On Fri, Nov 19, 2010 at 10:56 AM, Audra Rudys <sphin...@gmail.com> wrote: > >> This ought to be simple and yet... >> How do I write the following query in web2py? >> UPDATE dest, src >> SET dest.field = src.field >> WHERE src.dest_id = dest.id >> >> I only need to run it once. Tried via database administration module, but >> the problem seems to be that the source and destination field names are the >> same. >> >> Via code, if I write: >> db(db.dest.src_id == db.src.id).update(field = db.src.field) >> I get an error that SET doesn't know what to do. >> >> The syntax checker won't allow me to write (keyword can't be an >> expression): >> db(db.dest.src_id == db.src.id).update(dest.field = db.src.field) or >> db(db.dest.src_id == db.src.id).update(db.dest.field = db.src.field) >> >> I tried to execute the query using executesql and got an error as well: >> >> Traceback (most recent call last): >> >> >> File "E:\web2py\gluon\restricted.py", line 188, in restricted >> >> >> exec ccode in environment >> >> >> File "E:/web2py/applications/octopus/models/db_octopus.py" >> <http://127.0.0.1:8000/admin/default/edit/octopus/models/db_octopus.py>, >> line 44, in <module> >> >> >> db.executesql('UPDATE db.page_content,db.work SET >> db.page_content.created_by = db.work.created_by WHERE >> db.page_content.work_id == db.work.id;') >> >> >> File "E:\web2py\gluon\sql.py", line 1451, in executesql >> >> >> self._execute(query) >> >> >> File "E:\web2py\gluon\sql.py", line 963, in <lambda> >> >> >> self._execute = lambda *a, **b: self._cursor.execute(*a, **b) >> >> OperationalError: near ",": syntax error >> >> ... >> >> In case it's useful, variable a = ('UPDATE db.page_content,db.work SET >> page_content....d_by WHERE db.page_content.work_id == db.work.id;',) >> >> >> I've tried a few variations of the syntax, but still getting these errors. >> Please help! >> >> Audra >> >> >
