[Sqlalchemy-tickets] Issue #3591: Update with subselect on Sqlite produces bad SQL (zzzeek/sqlalchemy)

[Serge v. d. B. <iss...@bi...>]

New issue 3591: Update with subselect on Sqlite produces bad SQL
https://bitbucket.org/zzzeek/sqlalchemy/issues/3591/update-with-subselect-on-sqlite-produces

Serge van den Boom:

I have (in my simplified example) a table 'thing' with an entity 'value'.
I have another table, 'replacement', with columns 'old' and 'new'.
I wish to update 'thing.value' with 'new' from 'replacement', wherever 'thing.value' matches 'old'.

Because Sqlite does not support 'join' in 'update', nor 'update from', I use a subselect to achieve this, with the SqlAlchemy ORM functions:

    selectQuery = session.query(Replacement.new).filter(Replacement.old == Thing.value).subquery()                                   
    session.query(Thing).update({ Thing.value: selectQuery }, synchronize_session=False)

This produces an Sql error:

    sqlalchemy.exc.OperationalError: (pysqlite2.dbapi2.OperationalError) near "SELECT": syntax error [SQL: u'UPDATE thing SET value=SELECT replacement.new \nFROM replacement \nWHERE replacement.old = thing.value']
This is because Sqlite requires parentheses around the subselect.

A full example and a full stack trace are attached to this bug report.

Version information:

* SQLAlchemy 1.0.9 (via pip)
* Sqlite 3.9.1 (from Ubuntu, libsqlite3-0_3.9.1-2_amd64.deb)