SQLAlchemy correlated update for multiple columns

Question

I have been able to used the correlated update construct shown in the docs here to update one column in a table.

For example:

sel = select([UpdateTable.column]).\
      where(UpdateTable.id == OrigTable.id)

up = update(OrigTable).values(column=sel)

Which produces SQL like:

UPDATE origtable SET column=(SELECT updatetable.column
FROM updatetable
WHERE updatetable.id = origtable.id)

Is it possible to use the Declaritive or Query Api to update multiple columns for a selection?

I am trying to emulate something like the following in PostgreSQL:

UPDATE origtable
   SET
     column1 = updatetable.column1,
     column2 = updatetable.column2
   FROM updatetable 
   WHERE origtable.id == updatetable.id

EDIT:

It seems that formatting this a single statement may not be possible with the current SQLAlchemy api.

However, a workaround using two selects seems possible using the solution from here.

[This solution](http://stackoverflow.com/questions/30185056/updating-a-table-from-another-table-with-multiple-columns-in-sqlalchemy/30211905#30211905) is not optimal but might be closed enough for what you need. — lrnzcig, Sep 09 '15 at 11:15
Thanks @lrnzcig, I thought something like that might be possible. I guess the current api doesn't support this directly. — amckinley, Sep 09 '15 at 12:08
You're welcome. (If it helped, may I suggest a +1 for the solution linked. Thanks.) — lrnzcig, Sep 09 '15 at 14:26
http://stackoverflow.com/questions/42613777/sqlalchemy-correlated-update-with-multiple-columns — Matthew Moisen, Mar 06 '17 at 22:35

score 5 · Accepted Answer · edited May 23 '17 at 12:32

Since this is for Postgresql, we can use SQLAlchemy's multiple table update support. The documentation is for Core only, but the Query API is built atop Core, and we can apply that knowledge:

session.query(OrigTable).\
    filter(OrigTable.id == UpdateTable.id).\
    update({OrigTable.column1: UpdateTable.column1,
            OrigTable.column2: UpdateTable.column2},
           synchronize_session=False)

which results in

UPDATE origtable
SET column1=updatetable.column1,
    column2=updatetable.column2
FROM updatetable
WHERE origtable.id = updatetable.id

SQLAlchemy correlated update for multiple columns

1 Answers1