Thanks for confirming the DAL limitation here & suggestion. I ended up
doing that, and it worked.
On Fri, Nov 19, 2010 at 11:33 AM, mdipierro <mdipie...@cs.depaul.edu> wrote:
> Actually you cannot this with the DAL. Since this is a one-time step,
> I would do
>
> db.executesql("UPDATE dest SET dest.field = (SELECT src.field FROM
> src, dest WHERE src.dest_id = dest.id);")
>
>
> On Nov 19, 11:21 am, Audra Rudys <sphin...@gmail.com> wrote:
> > 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
> >
> >
>
