3
CREATE TABLE foo (
    name text NOT NULL,
    deleted_at timestamp without time zone
);

CREATE TABLE bar (
    name text NOT NULL,
    status_id int
);

UPDATE bar set status_id=1
FROM foo
WHERE status_id <> 1 AND foo.name = bar.name AND foo.deleted_at is null;

When I try to do this with the ORM, I end up with this error

InvalidRequestError: Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called

I want to use the ORM so that the session will be updated with the changes before the update command completes.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
boatcoder
  • 17,525
  • 18
  • 114
  • 178
  • Could you please also include the actual query you're trying to use? Your example is also incomplete in the sense that you cannot be using the ORM with those tables, as they lack primary keys, unless you're passing *name* as a candidate key in table args. – Ilja Everilä Mar 02 '17 at 09:57
  • I had boiled the much larger tables down to the minimum required to demonstrate the problem. – boatcoder Mar 02 '17 at 11:56

1 Answers1

5

Here's how you'd issue your multi table update using the ORM:

session.query(Bar).\
    filter(Bar.status_id != 1,
           Bar.name == Foo.name,
           Foo.deleted_at.is_(None)).\
    update({Bar.status_id: 1}, synchronize_session=False)

The footnote in the Query API documentation for update links to the Core multi table update docs:

The SQLAlchemy update() construct supports both of these modes implicitly, by specifying multiple tables in the WHERE clause

which expands to the ORM Query API as well, which is no surprise as the ORM is built on top of Core. The resulting query is:

UPDATE bar SET status_id=%(status_id)s
FROM foo
WHERE bar.status_id != %(status_id_1)s
  AND bar.name = foo.name
  AND foo.deleted_at IS NULL

From your error I suspect you have something along the lines of

session.query(Bar).select_from(Foo)...update(...)

which as the error states is not accepted. You have to pass the FROM table implicitly in the WHERE clause, e.g. filter() in the Query API.

To achieve

I'm wanting to use the ORM so that the session will be updated with the changes before the update command completes.

you'll have to change the synchronize_session accordingly to either 'fetch' or 'evaluate'.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
  • SqlAlchemy seems to NOT do so much automatically (making me call `aliased` for example) that I had discounted the ability of it to take an unjoined table in the filter statement. – boatcoder Mar 02 '17 at 11:58
  • In my previous job we were actually "bitten" by implicit table references in `filter()` causing some hefty implicit cross joins :P. Something to look out for... – Ilja Everilä Mar 02 '17 at 12:02