Support UPDATE ... SET ... FROM ... WHERE syntax (UPDATE FROM)

[Former user]

Attaching a patch (for 0.6 and for 0.7) to support this:

    u = table1.update().values(name=table2.c.othername)\
              .where(table2.c.otherid == table1.c.myid)

compiling to:

    UPDATE mytable SET name=myothertable.othername
    FROM myothertable WHERE myothertable.otherid = mytable.myid

(i.e. the FROM clause is inferred from the extra from-objects in the WHERE clause and the SET columns)

Unit tests included. Doesn't break any existing tests in either 0.6 or 0.7 (and I think this doesn't modify behavior in any use case that wasn't broken anyway).

[Former user]

Patch against rel_0_6

[Former user]

Patch against default branch

[Mike Bayer]

Cool. Can someone integrate whatever good ideas are in #1944? that's for the same functionality in MySQL mostly, it has various patches attached. I'd like to close one or the other as a dupe.

[Former user]

Seems to me that #1944 implements the same stuff as my patch, but more generally (mine is PG only, #1944 includes PG), with more tests, and probably more correctly: self.preparer.format_table(t) (will fail on selects?) vs. self.process(t, asfrom=True). So I think this one should be closed as a dupe. #1944 looks good except for lots of code duplication, both in tests and implementation. I'll take a stab at cleaning it up later this week.

[Former user]

(Mine is PG-only and not explicitly so; it will output the same SQL on engines that don't support it.)

[Mike Bayer]

that is great, yeah #1944 seemed to need cleanup. v. time consuming for me to get into new features patches like these since I have to really immerse in the desired behavior across all the backends to make sure its right, thanks for the help !

[Mike Bayer]

surprise ! Oracle has a completely crazy way to do the same thing!

UPDATE table SET (a, b) = (SELECT a, b FROM someothertable)

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/update_statement.htm

[Mike Bayer]

patch adds support + tests to Mysql, MSSQL, Postgresql. needs docs and is good enough for release.

[Mike Bayer]

diff:@0d3dbb39e13881c3d670ab61a23aa234c28dd21a:9c896906c7e4130ea11cf913dd50d29a9a3e1fa7

[Sok Ann Yap]

Looking at the Compatibility section in http://www.postgresql.org/docs/9.1/static/sql-update.html, Oracle is actually following the standard, for once. I think DB2 does the same also.

[Mike Bayer]

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 ?

[Sok Ann Yap]

No idea.. I have actually never written such SQL before, but may need to do so for DB2 later. Probably has to use something other than values() I guess. Anyway, I will go with raw SQL first :)

[Mike Bayer]

Removing milestone: 0.7.4 (automated comment)