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
