Update multiple columns from a subquery

Question

I'd like to create the following statement using SQLAlchemy. It can be fully executed on the database and thus does not involve any retrieval of data into the sqlalchemy layer.

UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
  (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
     WHERE d.group_id = s.group_id);

Your help is much appreciated

Also, should the WHERE clause be a part of the subquery, or is it meant to be part of the UPDATE (which at the moment does not at least in any obvious way join *data*)? In other words are the parentheses a bit off? — Ilja Everilä, Apr 20 '17 at 12:58

Ilja Everilä · Answer 1 · 2017-04-20T12:51:44.223

You need tuple_(), self_group(), and a correlated subquery:

In [9]: from sqlalchemy import tuple_

In [10]: stmt = select([func.sum(data.c.x),
    ...:                func.sum(data.c.y),
    ...:                func.avg(data.c.x),
    ...:                func.avg(data.c.y)]).\
    ...:     where(data.c.group_id == summary.c.group_id)

In [11]: summary.update().\
    ...:     values({ tuple_(summary.c.sum_x,
    ...:                     summary.c.sum_y,
    ...:                     summary.c.avg_x,
    ...:                     summary.c.avg_y).self_group(): stmt })
Out[11]: <sqlalchemy.sql.dml.Update object at 0x7ff76a80a908>

In [12]: print(_)
UPDATE summary SET (sum_x, sum_y, avg_x, avg_y)=(SELECT sum(data.x) AS sum_1, sum(data.y) AS sum_2, avg(data.x) AS avg_1, avg(data.y) AS avg_2 
FROM data 
WHERE data.group_id = summary.group_id)

The rest (executing etc.) is up to you, as the question is light on details.

Update multiple columns from a subquery

1 Answers1