Perl UPDATE Operation on any database means to update one or more records already available in the database tables. Following is the procedure to update all the records having SEX as 'M'. Here we will increase AGE of all the males by one year. This will take three steps −
my $sth = $dbh->prepare("UPDATE TEST_TABLE SET AGE = AGE + 1 WHERE SEX = 'M'"); $sth->execute() or die $DBI::errstr; print "Number of rows updated :" + $sth->rows; $sth->finish(); $dbh->commit or die $DBI::errstr;
There may be a case when condition is not given in advance. So you can use bind variables, which will take required values at run time. Perl DBI modules make use of a question mark in place of actual value and then the actual values are passed through execute() API at the run time. Following is the example −
$sex = 'M'; my $sth = $dbh->prepare("UPDATE TEST_TABLE SET AGE = AGE + 1 WHERE SEX = ?"); $sth->execute('$sex') or die $DBI::errstr; print "Number of rows updated :" + $sth->rows; $sth->finish(); $dbh->commit or die $DBI::errstr;
In some case you would like to set a value, which is not given in advance so you can use binding value as follows. In this example income of all males will be set to 10000.
$sex = 'M'; $income = 10000; my $sth = $dbh->prepare("UPDATE TEST_TABLE SET INCOME = ? WHERE SEX = ?"); $sth->execute( $income, '$sex') or die $DBI::errstr; print "Number of rows updated :" + $sth->rows; $sth->finish();