Search code examples
sql-serveransi-sqlsql

Is an update with a join standard sql compliant

Is the following standard SQL compliant? If not, then why not?

UPDATE a
SET    a.Y = 2
FROM   TABLE_A a 
       INNER JOIN TABLE_B b ON
          a.X = b.X
WHERE  b.Z = blahblah
Solution

  • The ANSI compliant way to write the query is:

    UPDATE TABLE_A
    SET Y = 2
    WHERE b.Z = blahblah AND
          EXISTS (SELECT 1 FROM TABLE_B b WHERE TABLE_A.X = b.X);

    To the best of my knowledge, neither ANSI nor ISO provide rationales for why they do not do something. I could speculate that the FROM clause causes issues when there are multiple matches on a given row. Personally, I would not want to be in the room during the arguments about which order the updates take place.