MSSQL correlated update - the table itself has to be in the FROM list if an alias exists there

[Sok Ann Yap]

Taken from http://msdn.microsoft.com/en-us/library/ms177523.aspx:

If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias.

So, omitting the table itself works when there is no alias for it:

UPDATE x SET name = y.name FROM y WHERE x.id = y.id; -- good

Adding the table itself works too:

UPDATE x SET name = y.name FROM x, y WHERE x.id = y.id; -- good

However, if there is an alias for the table, omitting the table itself doesn't work anymore:

UPDATE x SET name = y.name FROM y, x AS z WHERE x.id = y.id AND y.otherid = z.id; -- bad

Meanwhile, adding the table itself always works:

UPDATE x SET name = y.name FROM x, y, x AS z WHERE x.id = y.id AND y.otherid = z.id; -- bad

[Sok Ann Yap]

Patch for mssql dialect to always include the table itself in UPDATE statement whenever it has the FROM part

[Sok Ann Yap]

Ahh, the last comment should be -- good instead of -- bad.

[Mike Bayer]

interesting, I'll have to look at this tomorrow, thanks !

[Mike Bayer]

very nice - I added some actual round trip tests to all dialects for this as well in 541e59c3d7c141cfe532b26b5fbf4b8a8d30b841. I'm a little bothered by the fact that there were already UPDATE...FROM compilation tests in two places, test_compiler and test_update, not sure why that is, but that's a different issue. nice job !

[Mike Bayer]

Removing milestone: 0.7.7 (automated comment)