bory has asked for the wisdom of the Perl Monks concerning the following question:

Happy New Year for all the monks! I have the following problem:I want to delete from a database the records that are NOT equal with the elements of an array. 
my @test=("Anna","Maria","Carmen")
foreach $line(@test){    
                        
        my $sth=$dbh->prepare(qq{DELETE FROM tnt WHERE  `Name`!=\"$lin
+e\"});
        $sth->execute();
        $sth->finish;
        }

[download]
If I have in the database the record "Diana" I want to erase it because it's not an element of the array @test. In the way i do it it deletes all the records from the database. Thank you very much for your time.

Replies are listed 'Best First'.
Re: deleting from a mysql database
by edan (Curate) on Jan 06, 2004 at 12:08 UTC

    UNTESTED

    
my @array = qw(Anna Maria Carmen);
my @phldrs = ('?') x @array;

local $" = ',';
my $sql = qq/DELETE FROM tnt WHERE Name NOT IN (@phldrs)/;

my $sth = $dbh->prepare($sql);
$sth->execute(@array);
$sth->finish;

    [download]
    --
    3dan

[reply]
[d/l]
Re: deleting from a mysql database
by jonadab (Parson) on Jan 06, 2004 at 14:25 UTC

    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});
    }
  }

    [download]

    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$/

    [download]
[reply]
[d/l]
[select]
      Thank you very much jonadab.Now I understood where i made the mistake and with your great help it's working ok.Thank you
[reply]
Re: deleting from a mysql database
by tachyon (Chancellor) on Jan 06, 2004 at 12:10 UTC
    DELETE FROM table WHERE Name ! IN ( 'a', 'b', 'c' )

    cheers

    tachyon

[reply]
[d/l]
      Becareful /w large IN statements... they can slow down DB's if nested and/or are large.

      Update: they lack indexes, so where's on them can be quite slow.


      Play that funky music white boy..
[reply]
Re: deleting from a mysql database
by mpolo (Chaplain) on Jan 06, 2004 at 14:05 UTC

    Not really an answer to your question, but you don't need to escape (backslash) the quotation marks inside of a qq(). Also, the backticks around Name are unnecessary in mysql.

    The reason that your code doesn't work is that the first time through the loop, you delete everything that is not "Anna". The second time through, you delete everything that is not "Maria" (which is everything in the database). The third time, there is nothing left to delete...

    Another monk has already provided code using IN, which should be effective, but slow. It all depends on database sizes...

[reply]

Back to Seekers of Perl Wisdom