http://qs1969.pair.com?node_id=319123


in reply to deleting from a mysql database

Your code will remove all of the entries from the database (unless there is only one entry in @test), because for each element of @test it removes all the entries that are not equal to that element. You want to do the opposite: remove all the elements that are not represented in @test (i.e., not equal to _any_ of the entries in @test). So, you're looping over the wrong thing. Instead of looping over the list of things you don't want to delete, you want to loop over all the entries in the database and for each one you want to decide whether to delete it or not.

# This is untested. Test on sample data that # doesn't matter before using on live data. my %represented = map{$_=>1} @test; my $sth = $dbh->prepare("SELECT Name FROM tnt"); my $del=$dbh->prepare("DELETE FROM tnt WHERE Name=?"); $sth->execute(); while (my $line = $sth->fetchrow_hashref()) { if (not $represented{$$line{Name}}) { $del->execute($$line{Name}); } }

Update: minor optimization (prepare $del once outside the loop, instead of n times inside).


$;=sub{$/};@;=map{my($a,$b)=($_,$;);$;=sub{$a.$b->()}} split//,".rekcah lreP rehtona tsuJ";$\=$ ;->();print$/

Replies are listed 'Best First'.
Re: Re: deleting from a mysql database
by bory (Beadle) on Jan 06, 2004 at 15:12 UTC
    Thank you very much jonadab.Now I understood where i made the mistake and with your great help it's working ok.Thank you