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
)

score 4 · Accepted Answer · edited May 23 '17 at 12:02

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)

You are a hero; thank you! I would ask that you spam this answer on every `correlated update with multiple columns` question that you can find! — Matthew Moisen, Mar 06 '17 at 22:37
Is there any benefit to using your first approach over the second approach? — Matthew Moisen, Mar 06 '17 at 22:38
I guess not having to do the separate EXISTS test in order to not update unmatching rows in the 1st approach is a benefit compared to 2nd. Though the 1st approach might better support complex joined views, it is limited by [key preservation](http://docs.oracle.com/cd/E11882_01/server.112/e25494/views.htm#ADMIN11783). That might or might not force you to go for the 2nd approach in some cases. I have very little to no experience with Oracle, so I'll have to leave finding out the details to the reader. — Ilja Everilä, Mar 06 '17 at 23:07

SQLAlchemy Correlated Update with Multiple Columns?

1 Answers1

Linked

Related