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