MSSQL correlated update - the table itself has to be in the FROM list if an alias exists there
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
Comments (5)
-
reporter -
reporter Ahh, the last comment should be
-- good
instead of-- bad
. -
repo owner - changed milestone to 0.7.7
interesting, I'll have to look at this tomorrow, thanks !
-
repo owner - changed status to resolved
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 !
-
repo owner - removed milestone
Removing milestone: 0.7.7 (automated comment)
- Log in to comment
Patch for mssql dialect to always include the table itself in UPDATE statement whenever it has the FROM part