Re: Add Missing From?

In article <200408101156(dot)19796(dot)josh(at)agliodbs(dot)com>,
Josh Berkus <josh(at)agliodbs(dot)com> writes:

> Harald,
>> 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?

> Nope. In fact, I'd argue pretty strongly against any move to do so.

> MySQL supports multi-table delete for 2 reasons:
> 1) MySQL does not support CASCADE Foriegn Keys, and as a result this is the
> only way they can "clean out" all related records.

Not quite - MySQL implemented ON DELETE CASCADE for foreign keys
_before_ multi-table DELETEs.

> 2) To be blunt: MySQL doesn't care about your data.

I know - what do you think why I'm lurking here? ;-)

> Since we do support CASCADE FKs, there is no reason for us to support this
> syntax; just set your FKs up correctly and it's taken care of. Were we to
> consider implementing this, the implementors would have to answer the
> following questions:

> A) In what order are the rows deleted, from which table first?

In exactly the same order as for single-table DELETEs -
implementation-defined.

> B) If no join criteria are supplied, is it OK to delete all rows from one of
> the tables?

Yes - people creating Cartesian products deserve punishment :-)

> C) If one of the tables had FKs or triggers that affect one of the other
> tables, when do these get evaluated/fired?

Implementation-defined.

> Overall, I consider it a very, very bad idea.

My main concern was not multi-table DELETEs per se, but a way to do
deletions based on results of arbitrary queries. Multi-table DELETEs
would just be a logical extension to that.

I thought about something like that:

DELETE [tbl [,tbl]...]
FROM fromexp

"fromexp" could be anything which is legal after a "SELECT ... FROM",
including outer joins and LIMIT clauses.
"tbl" could be names or aliases of tables used in fromexp. If none
supplied, this would default to all tables used there, thus making
"DELETE FROM t1 WHERE whatever" just a degenerate case.

The semantics of that would be:
1. Do a "SELECT * FROM fromexp"
2. For every tbl, delete everything covered by the result set of the
SELECT, in some arbitrary order (unless restricted by an ORDER BY)