UPDATE if found, INSERT if not...

[c4n]

I need my Perl script to do this:

- read a file (1 word per line)
- if that word is in the database it would count +1
- if not it must add it to the database

I came up with this Perl script, but it doesn't work (it doesn't really matter that this is a Perl script, I guess there a re some problems with my SQL statements...):

$dbh = DBI->connect( "DBI:mysql:$database_host", "$username", "$pass", {RaiseError=>1,AutoCommit=>1});
open(F, "file.txt"
while(<F>){
chomp;
$sth = $dbh->do(qq{UPDATE `words` SET count=count+1 WHERE `text` LIKE '$_';}) or
$sth = $dbh->do(qq{INSERT INTO `words` (`text`, `count`) VALUES ('$_', '1')});
}
close(F);

If I delete the &quot;UPDATE&quot; statement it works ( = adds the new values to the table), but I don't want the same words repeated in the table, I want the count to increment. Maybe have some other SQL to suggest?

Thanks for your help!

 

[hvass]

sql looks ok
perl is not my language but $dbh->do will return &quot;0E0&quot; if the number of rows affected is zero which evaluates to zero but is considered true

$sth = $dbh->do(qq{UPDATE `words` SET count=count+1 WHERE `text` LIKE '$_';}) or
$sth = $dbh->do(qq{INSERT INTO `words` (`text`, `count`) VALUES ('$_', '1')});

I think this means $sth gets set as true even if the sql returned zero rows so your code never gets to do the insert

try it long handed
$rows = $dbh->do(qq{UPDATE `words` SET count=count+1 WHERE `text` LIKE '$_';})
if ($rows==0) then do the insert

 

[c4n]

Thanks, I already got it working the long way

$sth = $dbh->prepare(qq{SELECT `count` FROM `words` WHERE `text` = '$_'});
$sth->execute();
($count) = $sth->fetchrow_array or
$sth = $dbh->do(qq{INSERT INTO `words` (`text`, `count`) VALUES ('$_', '0')});
$sth = $dbh->do(qq{UPDATE `words` SET count=count+1 WHERE `izraz` LIKE '$_'});