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?

Did you solve the issue? I have something almost identical and I'm struggling — Fuxi
– Fuxi, Commented Feb 14, 2016 at 14:46
Had to move on with a workaround (two queries instead of one). Upvote the question if you're struggling with the same, maybe someone will see it. — ValAyal
– ValAyal, Commented Feb 16, 2016 at 16:24

Myles · Accepted Answer · 2017-02-08 01:20:08Z

16

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.

answered Feb 8, 2017 at 1:20

Myles

4361 gold badge5 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ilja Everilä Over a year ago

Note that the example here is not a multi table update, but the standard SQL way to refer to multiple tables using a sub query.

Fuxi · Accepted Answer · 2016-02-24 08:38:08Z

12

I think I had exactly the same problem. Here is my solution:

query = update(Model).values(field=123)
query = query.where(Model.parent_model_id == ParentModel.id)
query = query.where(ParentModel.grand_parent_id == GrandParentModel.id)
query = query.where(GrandParentModel.name == 'foobar')
session.execute(query)

answered Feb 24, 2016 at 8:38

Fuxi

5,5483 gold badges27 silver badges37 bronze badges

2 Comments

Charles L. Over a year ago

This doesn't work for sqlite, but probably does for other DB backends

Christopher Stevens Over a year ago

Years later (2023), this is working for me while using an sqlite database.

Collectives™ on Stack Overflow

Update joined table via SQLAlchemy ORM using session.query

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related