Is an update with a join standard sql compliant

Question

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

No it's not standard compliant. There is no FROM in the definition of the UPDATE statement in the SQL standard. Why? Because it was not specified. — , Aug 28 '15 at 09:49
@a_horse_with_no_name upped the comment as it raised a smile - although answering "why not...just because it isn't" doesn't usually help. I realize an answer might be just a stab in the dark as definite reason's why X is included but Y isn't is just supposition ... but I'm interested in possible reasons. — whytheq, Aug 28 '15 at 10:00
It's not ANSI compliant (as to why not, you'd have to ask the ANSI committee). See this answer for ANSI and non ANSI methods http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — Steve Ford, Aug 28 '15 at 10:11

score 4 · Accepted Answer · answered Aug 28 '15 at 11:01

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.

Is an update with a join standard sql compliant

1 Answers1