On 21-Jun-01 Chris Petersen wrote:
>> here, @ids is the array, delete in chunks of 64:
>> while ( my(@id)= splice(@ids,0,64)) {
>> $qry="DELETE FROM master WHERE master_id in (".join(',',@id).")";
>> SQLQuery($qry);
>> }
>
> I thought about this, too.. But Mark said that he needed to delete from the
> table where the ID's don't match. If you break it into chunks, then you'd
> end up deleting everything that doesn't match that first chunk, which kills
> pretty much the entire table.
>
I prolly wasn't too clear: @ids is the array of all non-matches.
i.e the list you want to delete (& i'm assuming id is the primary key).
@ids=GetArray("SELECT a.id
FROM master as a LEFT JOIN new_tbl as b ON a.id=b.id
WHERE b.id IS NULL");
> The other option would be to create a new field in the table, something like
> "dontdeleteme TinyInt Unsigned Not Null Default 0"... Then use your splice
> technique to do something like:
>
> $dbh->do('UPDATE master SET dontdeleteme=0');
> while ( my(@id)= splice(@ids,0,64)) {
> $dbh->do("UPDATE master SET dontdeleteme=1 WHERE master_id in
> (".join(',',@id).")");
> }
;>
Which is _exactly_ how i use it :
# Mark all active webmasters (traffic in the last month)
# without busting max_packet_size
my @ids=GetArray("SELECT distinct id from hits
WHERE hitdate >= DATE_SUB(current_date, INTERVAL 1 MONTH)");
SQLQuery("UPDATE webmaster set active=0");
while ( my(@id)= splice (@ids,0,64)) {
$qry="UPDATE webmaster set active=1
WHERE wmid in (" .join(',',@id).")";
SQLQuery($qry);
}
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
---------------------------------------------------------------------
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
