On Tue, 10 Aug 2004, Harald Fuchs wrote:
> In article <16580.1092071243@sss.pgh.pa.us>,
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> > Jan Wieck <JanWieck@Yahoo.com> writes:
> >> What about
> >> DELETE FROM staff JOIN users ...
> >> then?
>
> > I don't much care for that, mainly because in my mind "x JOIN y" should
> > always be semantically equivalent to "y JOIN x". I think we want a real
> > clear syntactical separation between the deletion target table and the
> > other tables.
>
> Just one minor nit:
>
> You're talking about "the deletion target table". Sorry to mention
> the M word again, but MySQL allows deleting from more than one table
> at the same time. Should we support that?
I don't think we should unless we can give a good definition to its
behavior. The message (on -sql?) on UPDATE...FROM doing not what the user
expected when the join wasn't 1-1 gives a good example of some of the
issues that come from these sorts of extensions.
I don't see anything in the MySQL delete syntax docs that talks about what
happens in that case. If you do:
delete from foo, bar where bar.val=3;
Is that an error, is it only an error if one of foo or bar has more than
1 row? In the case both have more than 1 row, does it delete all rows in
foo or none or something else?