Update with subselect on Sqlite produces bad SQL

[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)

[Mike Bayer]

please use "as_scalar()" when a subquery is to return a single scalar value:

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

output:

UPDATE thing SET value=(SELECT replacement.new 
FROM replacement 
WHERE replacement.old = thing.value)

docs:

http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#scalar-selects

[Serge van den Boom]

Ah! Thanks.