python - SQLAlchemy Correlated Update with Multiple Columns?

Question

This question has been asked multiple times on stackoverflow, but they all appeared to be over a year old so I figured I would ask again in case there has been an update.

A correlated update is an update statement that updates all rows in one table based on values from another table, while linking the two tables together.

From the SQLAlchemy docs, we can do a correlated update easily but only on a single column:

update(foo).values(bar=select([foobar.c.bar]).where(foobar.c.id == foo.c.id))

This translates into:

UPDATE foo
SET bar = (
    SELECT bar
    FROM foobar
    WHERE foobar.id = foo.id
) 

How can we write a correlated update using more than one column in sqlalchemy? For example:

UPDATE foo
SET (bar, baz) = (
    SELECT bar, baz
    FROM foobar
    WHERE foobar.id = foo.id
) 

Answer 1

Based on your avatar and description I'm guessing you're using Oracle. From this answer one can device the following SQLAlchemy concotion, if your join results in a key preserved view:

stmt = select([foo.c.bar.label('foo_bar'),
               foo.c.baz.label('foo_baz'),
               foobar.c.bar.label('foobar_bar'),
               foobar.c.baz.label('foobar_baz')]).
    where(foo.c.id == foobar.c.id)

update(stmt).values({stmt.c.foo_bar: stmt.c.foobar_bar,
                     stmt.c.foo_baz: stmt.c.foobar_baz})

which produces the following SQL:

UPDATE (SELECT foo.bar AS foo_bar,
               foo.baz AS foo_baz,
               foobar.bar AS foobar_bar,
               foobar.baz AS foobar_baz
        FROM foo, foobar
        WHERE foo.id = foobar.id)
SET foo_bar=foobar_bar, foo_baz=foobar_baz

The labels are important since your tables share column names.

You can also produce your original target SQL:

from sqlalchemy import tuple_, select, exists

stmt = select([foobar.c.bar, foobar.c.baz]).where(foo.c.id == foobar.c.id)
foo.update().
    values({tuple_(foo.c.bar, foo.c.baz).self_group(): stmt}).
    where(exists(stmt))

The self_group() call is important, as the compiler seems to omit the parentheses around the tuple, producing incorrect syntax, in this case. I added the WHERE clause in order to avoid updating foo rows with no matching foobar:

UPDATE foo SET (bar, baz)=(SELECT foobar.bar, foobar.baz 
FROM foobar 
WHERE foo.id = foobar.id) WHERE EXISTS (SELECT foobar.bar, foobar.baz 
FROM foobar 
WHERE foo.id = foobar.id)