> I can't answer why, but I can tell you it's fraught with potential > error. Inventing syntax runs the risk of supporting undesirable > behavior. > > SQL Server has such a syntax. Unfortunately, when you say: > > update T ... from S
Pretty much everything does. Yes, Sybase Transact SQL was first, but that is only of historical significance. > the result is nondeterministic if more than one row in S matches. The > update applies all rows in S matching T. Of course, only the last one > is preserved. Of course, because order is nonsemantic, there's no way > to know *which* S row will "win". And the problem is exactly what? The statement will do exactly and precisely what it has been instructed to do. That it was given bad instruction by someone incompetent to be giving instruction is irrelevant. Perhaps it is the observer (you) who has failed to understand what it is that is being attempted. The real problem with implementing this sort of update is that it in order to prevent implementation errors (caused by the fact that the set algebra is performed one row at a time, and not as a set) you have to generate and intermediate update set and then apply that set after the fact -- if a column being updated is used in a join constraint (but not if it is used in a selection constraint).
