multiple columns in correlated subquery?

[Stephen Emslie]

In Oracle, I would like to perform a correlated subquery where
multiple columns are specified in the set clause and those columns are
selected by the subquery. For example:

UPDATE table1 a
SET (a.column1, a.column2) = (SELECT b.column1, b.column2 FROM table2
b WHERE a.id=b.id)
WHERE a.group=:group AND exists(select 1 where a.id=b.id);

Is this possible using sqlalchemy core? I have tried a few variations,
but nothing seems to do quite what I'm looking for. In the meantime
I've been doing the equivalent of:

UPDATE table1 a SET
a.column1 = (SELECT a.column1 FROM table2 b WHERE a.id=b.id),
a.column2 = (SELECT a.column2 FROM table2 b WHERE a.id=b.id)
WHERE a.group=:group AND exists(select 1 where a.id=b.id);

With something like this:

upd = table1.update()
upd = upd.where(table1.group == group)
upd = upd.where(exists(select([1], table1.c.id == table2.c.id)))
for col in table2.columns:
query = select([col])
query = query.where(table2.c.id == table1.c.id)
upd = upd.values({col.name: query})

I am interested in doing this as it would produce far fewer sub-
queries, and hopefully this would show a benefit in performance.

[Michael Bayer]

This is a completely Oracle-specific syntax I've never seen before, and apparently resembles an UPDATE FROM - ticket #2166 has been updated to see if it can be part of the solution when we implement the more common UPDATE FROM extension.

Unfortunately this syntax is not supported. Your current usage of individual scalar subqueries is the SQL standard version and is the most straightforward way. Or of course using straight text.

You can also write a custom compiler for Update:

from sqlalchemy import and_, select
from sqlalchemy.sql.expression import table, column, UpdateBase, _generative
from sqlalchemy.ext.compiler import compiles

class CustomUpdate(UpdateBase):
def __init__(self, table, source, dest):
self.table = table
self.source = source
self.dest = dest
self._whereclause = None

@_generative
def where(self, whereclause):
if self._whereclause is not None:
self._whereclause = and_(self._whereclause,whereclause)
else:
self._whereclause = whereclause

@compiles(CustomUpdate)
def _cust_update(elem, compiler, **kw):
t = "UPDATE " + compiler.process(elem.table, asfrom=True) + \
" SET (" + ",".join(
compiler.process(c) for c in elem.dest
) + ") = (" + compiler.process(elem.source.correlate(elem.table)) + ")"
if elem._whereclause is not None:
t += " WHERE " + compiler.process(elem._whereclause)

return t

t1 = table('t1', column('a'), column('b'), column('c'))
t2 = table('t2', column('a'), column('b'))

print CustomUpdate(
t1,
select([t2.c.a, t2.c.b]).where(t2.c.b==t1.c.a),
[t1.c.a, t1.c.b]
).where(t1.c.c==12)

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>