#2166: Support UPDATE ... SET ... FROM ... WHERE syntax (UPDATE FROM)
-----------------------------------+-------------------------------
Reporter: guest | Owner: zzzeek
Type: enhancement | Status: closed
Priority: medium | Milestone: 0.7.4
Component: sql | Severity: major - 1-3 hours
Resolution: fixed | Keywords:
Progress State: completed/closed |
-----------------------------------+-------------------------------
Comment (by zzzeek):
that's too bad since the apparently SQL standard syntax is more difficult
to support, unless we can make some big assumptions about the WHERE
clause. If we can stick the entire WHERE clause into the sub-select, then
it's not so hard. But if we have to surgically break up the WHERE clause
into clauses that correlate to the FROM table and those that don't, then
it's a crapshow:
{{{
accounts.update().
values(contact_last_name=salesmen.c.last_name,
contact_first_name=salesmen.c.first_name).
where(salesmen.c.id==accounts.c.sales_id).where(accounts.c.status=='Y")
}}}
becomes:
{{{
UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id=accounts.sales_id AND accounts.status='Y')
}}}
or
{{{
UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id=accounts.sales_id) WHERE accounts.status='Y'
}}}
?
do you have insight on this ?
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2166#comment:10>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|