>
> Oops, for logical clarity, it should be:
> UPDATE dest
> SET dest.field =
> (SELECT src.field
>
FROM src, dest
>
WHERE src.id = dest.src_id);
>
(Please disregard earlier message about logical clarity, it sent before I
had a chance to revise the query!)
>
> On Fri, Nov 19, 2010 at 11:20 AM, Audra Rudys <[email protected]> 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 <[email protected]> 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
>>>
>>>
>>
>
