Why not do something like:
my $q = "DELETE FROM master WHERE master_id NOT IN (";
$q .= join(',', @masteridarray);
$q .= ')';
$dbi_dbh->do($q);
Though this will obviously fall into the same size limitations that you were
doing, but it should execute a lot faster, and be a bit smaller than your
example. (I don't have an answer for this, being rather new to the mysql
community myself).
-Chris
> Hello,
>
> I have a table with approximately 68,000 records. I then get a file
> uploaded to the server with approximately 68,000 master_id's listed in it.
> If the master_id's in our MySQL table are not one of the master_id's listed
> in the file, they need to be deleted from the MySQL table.
>
> Right now, a Perl script reads the master_id's from the uploaded file, and
> puts them all into an array in memory. We then loop through all the
> master_id's from the MySQL table. If the MySQL master_id is not in the
> array of master_id's from the file, then the record is deleted. This works
> fine if we are using a small amount of records, but there are over 68,000 of
> them. It seems like there should be a better, more efficient way of doing
> this.
>
> One thought that I had, was to create one big huge DELETE statement.
> Something like this:
>
> $q = "DELETE FROM master";
>
> $where = " WHERE ( ";
> $morethan1 = 0;
> foreach (@masteridarray) { #this is our 68,000 valid id's
> if ($morethan1) {
> $where .= " OR master_id != $_ ";
> }else{
> $where .= " master_id != $_ ";
> $morethan1 = 1;
> }
> }
> $where .= ")";
> if ($morethan1) {
> $q .= $where;
> }
>
> my $sth = $dbi_dbh->prepare($q);
> $sth->execute;
>
> However, I wasn't sure if MySQL could process something like this. And if
> it could, how much bigger could the query statement get (the number of
> records in the table will easily exceed 100,000 before the end of the year,
> and continue to grow even bigger next year)?
>
> Another thought/question was whether MySQL had some mechanism for searching
> through the upload file itself. Something along the lines of, "DELETE FROM
> master where master_id != [any of the masterid's in file ./id.txt]".
>
> The number of records in this table is only going to grow over the coming
> months, so, I want to make sure that I have a solution that is scalable.
> Any/all suggestions will be greatly appreciated!
>
> Mark
>
>
> Imagine your own dedicated server, but ...
> without all the hassles of managing it yourself!
> Managed Dedicated Servers
> http://www.ServerPros.com
>
> --
> Mark A. Sharkey
> PrecisionPros.com Network
> 6543 East Omega Street
> Mesa, Arizona 85215
> 800 844 4434 toll free
> 480 461 9765 local
> 480 461 9312 fax
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
