[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