[PLUG] Re: MySQL Statement?

Randal L. Schwartz merlyn at stonehenge.com
Thu Jul 28 18:54:44 UTC 2005


>>>>> "Steven" == Steven D Coffman <s.coffman at tradetrans.net> writes:

Steven> I need to clean up our database. The tables and files are a bit
Steven> complicated but the general gist of what I need to do is like this
Steven> (sorry.. best way I can explain it is psuedo code)

Steven> Select Atable where Company = 1212 and Date < "2005-01-01"
Steven>   ** ok, this is the basic select I want from the file and I only need
Steven> the "fileID" from this table **

Steven> *** Once all of the fileID's from the above select has been performed, I
Steven> want to run a delete routine for every fileID from that first select ***
Steven> For (?? Every fileID)
Steven>   Delete tableB, Delete tableC....etc.
Steven>   Next fileID

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

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.

Of course, friends don't let friends use MySQL any more.

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn at stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!




More information about the PLUG mailing list