>> In MySQL I can update 2 tables (parent, child) with a statement like this
>>
>> UPDATE parent LEFT JOIN child ON parent.pid = child.foreign_key SET
>> parent.field1 = 'company',
>> child.field2 = 'john'
>> WHERE child.pid = 7
One has to wonder what the above construction is even intended to mean.
Since it's a LEFT JOIN, presumably there can be rows coming out of the
join that have a "parent" but no "child" row. What does it mean to
update child.field2 when there's no child row? You could make about
equally good arguments for raising an error, updating the parent side
only, or updating neither.
Even without a LEFT JOIN, I don't understand what's expected to happen
when the same row in one table joins to multiple rows in the other table.
One advantage of following standards is that the standards have usually
been thought through in some detail (though there are crannies of the
SQL spec that hardly seem to deserve that description :-(). This thing
has not been thought through. I'm sure the actual behavior of the
corner cases in MySQL is just whatever happened to fall out of their
implementation.
regards, tom lane