Database UPDATE Operation in Perl

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 −

• Preparing SQL query based on required conditions. This will be done using prepare() API.
• Executing SQL query to select all the results from the database. This will be done using execute() API.
• Releasing Statement handle. This will be done using finish() API.
• If everything goes fine then commit this operation otherwise you can rollback complete transaction. See next section for commit and rollback APIs.

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;

Using Bind Values

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

Mohd Mohtashim

Published on 02-Dec-2019 07:52:37

• Related Questions & Answers
• Database INSERT Operation in Perl
• Database READ Operation in Perl
• Database DELETE Operation in Perl
• Database Update Operation in Python
• Using NULL Values in Perl Database Operation
• Database INSERT Operation in Python
• Database READ Operation in Python
• Database DELETE Operation in Python
• How to create Database Connection in Perl?
• Convert a field to an array using update operation in MongoDB
• Convert a field to an array using MongoDB update operation?
• How to update two columns in a MySQL database?
• How can I update child objects in MongoDB database?
• How to update data in a MySQL database with Java?
• What is update operation on the cursor having JOIN between 2 tables?