Update joined table via SQLAlchemy ORM using session.query

Question

Working with MySQL, I'd like to generate this SQL:

UPDATE tableA
INNER JOIN tableB
ON tableA.some_id = tableB.some_id
SET tableA.foo = 1
WHERE tableB.bar IN ('baz','baaz')

This is my SQLAlchemy query:

session.query(tableA).join(tableB, tableA.some_id == tableB.some_id) \
                     .filter(tableB.bar.in_(['baz','baaz']))\
                     .update({tableA.foo: 1})

But the SQL it generates is this (a multi-table update, with no join condition, which is not what I want):

UPDATE tableA, tableB 
SET tableA.foo = 1
WHERE tableB.bar IN ('baz','baaz')

I've tried changing the .join into another .filter to specify the join condition, that didn't solve the problem. How do I force this simple update statement to do the proper join?

Answer 1

As of version 0.7.4 sqlalchemy.sql.expression.update does allow you to refer to multiple tables in the WHERE clause. With this, you could build and execute an expression like:

users.update().values(name='ed').where(
        users.c.name==select([addresses.c.email_address]).\
                    where(addresses.c.user_id==users.c.id).\
                    as_scalar()
        )

(example straight from the link above)

The problem ValAyal is having is actually because Query.join() is not supported with Query.update(). Unfortunately, until 0.9.1 this was silently generating queries like the one ValAyal shared above. The changelog notes for 0.9.1 notes that the behavior was modified to emit a warning:

[orm] [bug] Query doesn’t support joins, subselects, or special FROM clauses when using the Query.update() or Query.delete() methods; instead of silently ignoring these fields if methods like Query.join() or Query.select_from() has been called, a warning is emitted. As of 1.0.0b5 this will raise an error.

References: #3349

We actually ran into this where I work just this evening and found that our code is, in fact, emitting the following warning (which says it will an error in 1.0):

SAWarning: Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called.  This will be an exception in 1.0
  self._validate_query_state()

In our case, we opted to convert the update into a select and an update to one table.