Database DELETE Operation in Perl

Perl DELETE operation is required when you want to delete some records from your database. Following is the procedure to delete all the records from TEST_TABLE where AGE is equal to 30. This operation will take the following steps.

• Preparing SQL query based on required conditions. This will be done using prepare() API.
• Executing SQL query to delete required records from the database. This will be done using execute() API.
• Releasing Stattement handle. This will be done using finish() API.
• If everything goes fine then commit this operation otherwise you can rollback complete transaction.

$age = 30;
my $sth = $dbh->prepare("DELETE FROM TEST_TABLE WHERE AGE = ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Using do Statement

If you're doing an UPDATE, INSERT, or DELETE there is no data that comes back from the database, so there is a short cut to perform this operation. You can use do statement to execute any of the command as follows.

$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');

do returns a true value if it succeeded, and a false value if it failed. Actually, if it succeeds it returns the number of affected rows. In the example it would return the number of rows that were actually deleted.