Perl MySQL Delete Data

Summary: in this tutorial, you will learn how to delete data in a MySQL table by using Perl DBI.

You use the DELETE statement to delete one or more rows in a table. To delete all rows in a table, you use the DELETE statement without the WHERE clause. To delete all rows in a big table more efficiently, you use the TRUNCATE TABLE statement.

For deleting rows in related tables, you use the DELETE JOIN statement or ON DELETE CASCADE referential action of a foreign key.

Perl MySQL delete data steps

To delete data in a MySQL table from a Perl program, you need to use the following steps:

First, use the  connect() method to connect to the MySQL database:

my $dbh = DBI->connect($dsn,$username,$password, \%attr);Code language: Perl (perl)

Next, to delete one row from a table, you use the DELETE statement with a WHERE clause. You need to put the placeholders ( ? ) in the DELETE statement in order to pass the values from the program to the statement. The placeholders (?) will be substituted by the values.

my $sql = "DELETE FROM table_name WHERE id=?";
my $sth = $dbh->prepare($sql);Code language: Perl (perl)

Then, call the  execute() method of the statement handle object to execute the query. You also need to pass the arguments, which replace the placeholders in the DELETE statement, to the  execute() method.

$sth->execute($id);Code language: Perl (perl)

After that, disconnect from the MySQL database.

$dbh->disconnect();Code language: Perl (perl)

Finally, you can display a message to specify that the row has been deleted successfully.

Perl MySQL deletes data examples

We’ll make a copy of the links table for the demonstration.

CREATE TABLE clinks
SELECT * FROM links;Code language: SQL (Structured Query Language) (sql)

MySQL created a new table named clinks with the same structure and data as the links table.

The following is the content of the clinks table:

SELECT * FROM clinks;Code language: SQL (Structured Query Language) (sql)

The following program deletes the link with id 1 first and then truncates the clinks table.

#!/usr/bin/perl
use strict;
use warnings;
use v5.10; # for say() function

use DBI;

say "Perl MySQL Delete Data Demo";

# MySQL database configurations
my $dsn = "DBI:mysql:perlmysqldb";
my $username = "root";
my $password = '';

# connect to MySQL database
my %attr = (PrintError=>0, RaiseError=>1);

my $dbh = DBI->connect($dsn,$username,$password, \%attr);

# delete 1 row with id 1
# say "Link with id 1 deleted successfully!" if delete_one_row($dbh,1);

# delete all rows in the clinks table
# say "All links deleted successfully!" if delete_all_rows($dbh);

# disconnect from the MySQL database
$dbh->disconnect();

sub delete_one_row {
    # delete one row from table
    # $dbh: database handle
    # $link_id: id of the link that need to delete
    my($dbh,$link_id)  = @_;
    my $sql = "DELETE FROM clinks WHERE link_id = ?";
    my $sth = $dbh->prepare($sql);
    return $sth->execute($link_id);
}

sub delete_all_rows {
    # delete all rows in the clinks table
    my($dbh) = @_;
    my $sql = "TRUNCATE TABLE clinks";
    my $sth = $dbh->prepare($sql);
    return $sth->execute(); 
}Code language: Perl (perl)

First, remove the comment of the call to the  delete_one_row() subroutine and execute the program. We got the following message:

Perl MySQL Delete Data Demo
Link with id 1 deleted successfully!Code language: JavaScript (javascript)

Check the clinks table to verify the deletion:

SELECT * FROM clinks;Code language: SQL (Structured Query Language) (sql)

Second, comment on the call to the  delete_on_row() subroutine, remove the comment of the call to the  delete_all_rows() subroutine, and execute the program:

Perl MySQL Delete Data Demo
All links deleted successfully!

Query the clinks table again to verify the truncate table operation:

SELECT * FROM clinks;Code language: SQL (Structured Query Language) (sql)

It returns no row as expected.

In this tutorial, you have learned how to use Perl DBI to delete data in a table of a MySQL database.