[PLUG] Re: MySQL Statement?

[AthlonRob]

Randal L. Schwartz wrote:
> With a *real* database (hint: "not MySQL"), referential cascading delete
> would take care of that.  Short of that, you could do something like:

How real does a database need to be before you consider it a real
database?  ;-)

> DELETE
> FROM tableb
> WHERE fileid IN (
>   SELECT fileid
>   FROM tablea
>   WHERE company = 1212 AND date < "2005-01-01"
> )
> 
> Presuming MySQL now supports subselects (which I think it has, finally
> catching it up with the 90s).  Repeat this as needed for each of your
> other tables.

That's a perfect example of why MySQL hasn't supported subqueries for so
long.  People who don't know what they're doing over-use them when joins
make far more sense.

How about something like:

DELETE tableb
FROM tableb
LEFT JOIN tablea
ON tableb.fileid = tableb.fileid
WHERE tablea.company = 1212 AND tablea.date < "2005-01-01"

That's what my one year of SQL experience (that is to say, very little)
tells me to use.  Warning: it is untested.  :-)

Rob