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

Issue #2468 resolved
Sok Ann Yap created an issue

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)

  1. Mike Bayer repo owner

    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 !

  2. Log in to comment