Re: Add Missing From?

On Tue, 10 Aug 2004, Harald Fuchs wrote:

> In article <16580(dot)1092071243(at)sss(dot)pgh(dot)pa(dot)us>,
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > Jan Wieck <JanWieck(at)Yahoo(dot)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?