{"id":3025,"date":"2013-01-17T20:22:53","date_gmt":"2013-01-18T04:22:53","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3025"},"modified":"2023-09-24T21:11:40","modified_gmt":"2023-09-25T04:11:40","slug":"perl-mysql-delete-data","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/perl-mysql\/perl-mysql-delete-data\/","title":{"rendered":"Perl MySQL Delete Data"},"content":{"rendered":"\n

Summary<\/b>: in this tutorial, you will learn how to delete data in a MySQL table by using Perl DBI.<\/p>\n\n\n\n

You use the DELETE statemen<\/a>t to delete one or more rows in a table. To delete all rows in a table, you use the DELETE<\/code> statement without the WHERE clause<\/a>. To delete all rows in a big table more efficiently, you use the TRUNCATE TABLE statement<\/a>.<\/p>\n\n\n\n

For deleting rows in related tables, you use the DELETE JOIN statement<\/a> or ON DELETE CASCADE<\/a> referential action of a foreign key<\/a>.<\/p>\n\n\n\n

Perl MySQL delete data steps<\/h2>\n\n\n\n

To delete data in a MySQL table from a Perl program, you need to use the following steps:<\/p>\n\n\n\n

First, use the  connect()<\/code> method to connect to the MySQL database:<\/p>\n\n\n

my<\/span> $dbh = DBI->connect<\/span>($dsn,$username,$password, \\%attr);<\/code><\/span>Code language:<\/span> Perl<\/span> (<\/span>perl<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, to delete one row from a table, you use the DELETE<\/code> statement with a WHERE<\/code> clause. You need to put the placeholders ( ?<\/code> ) in the DELETE<\/code> statement in order to pass the values from the program to the statement. The placeholders (?) will be substituted by the values.<\/p>\n\n\n
my<\/span> $sql = \"DELETE FROM table_name WHERE id=?\"<\/span>;\nmy<\/span> $sth = $dbh->prepare($sql);<\/code><\/span>Code language:<\/span> Perl<\/span> (<\/span>perl<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, call the  execute()<\/code> method of the statement handle object to execute the query. You also need to pass the arguments, which replace the placeholders in the DELETE<\/code> statement, to the  execute()<\/code> method.<\/p>\n\n\n
$sth->execute($id);<\/code><\/span>Code language:<\/span> Perl<\/span> (<\/span>perl<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, disconnect from the MySQL database.<\/p>\n\n\n
$dbh->disconnect();<\/code><\/span>Code language:<\/span> Perl<\/span> (<\/span>perl<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, you can display a message to specify that the row has been deleted successfully.<\/p>\n\n\n\n
Perl MySQL deletes data examples<\/h2>\n\n\n\n
We’ll make a copy of the links<\/code> table for the demonstration.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> clinks\nSELECT<\/span> * FROM<\/span> links;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL created a new table named clinks<\/code> with the same structure and data as the links<\/code> table.<\/p>\n\n\n\n
The following is the content of the clinks<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> clinks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Perl<\/figure>\n\n\n\n
The following program deletes the link with id 1 first and then truncates the clinks<\/code> table.<\/p>\n\n\n
#!\/usr\/bin\/perl<\/span>\nuse<\/span> strict;\nuse<\/span> warnings;\nuse<\/span> v5.10<\/span>; # for say() function<\/span>\n\nuse<\/span> DBI;\n\nsay<\/span> \"Perl MySQL Delete Data Demo\"<\/span>;\n\n# MySQL database configurations<\/span>\nmy<\/span> $dsn = \"DBI:mysql:perlmysqldb\"<\/span>;\nmy<\/span> $username = \"root\"<\/span>;\nmy<\/span> $password = ''<\/span>;\n\n# connect to MySQL database<\/span>\nmy<\/span> %attr = (PrintError=><\/span>0<\/span>, RaiseError=><\/span>1<\/span>);\n\nmy<\/span> $dbh = DBI->connect<\/span>($dsn,$username,$password, \\%attr);\n\n# delete 1 row with id 1<\/span>\n# say \"Link with id 1 deleted successfully!\" if delete_one_row($dbh,1);<\/span>\n\n# delete all rows in the clinks table<\/span>\n# say \"All links deleted successfully!\" if delete_all_rows($dbh);<\/span>\n\n# disconnect from the MySQL database<\/span>\n$dbh->disconnect();\n\nsub<\/span> delete_one_row<\/span> <\/span>{\n    # delete one row from table<\/span>\n    # $dbh: database handle<\/span>\n    # $link_id: id of the link that need to delete<\/span>\n    my<\/span>($dbh,$link_id)  = @_;\n    my<\/span> $sql = \"DELETE FROM clinks WHERE link_id = ?\"<\/span>;\n    my<\/span> $sth = $dbh->prepare($sql);\n    return<\/span> $sth->execute($link_id);\n}\n\nsub<\/span> delete_all_rows<\/span> <\/span>{\n    # delete all rows in the clinks table<\/span>\n    my<\/span>($dbh) = @_;\n    my<\/span> $sql = \"TRUNCATE TABLE clinks\"<\/span>;\n    my<\/span> $sth = $dbh->prepare($sql);\n    return<\/span> $sth->execute(); \n}<\/code><\/span>Code language:<\/span> Perl<\/span> (<\/span>perl<\/span>)<\/span><\/small><\/pre>\n\n\n
First, remove the comment of the call to the  delete_one_row()<\/code> subroutine and execute the program. We got the following message:<\/p>\n\n\n
Perl MySQL Delete Data Demo\nLink with<\/span> id 1<\/span> deleted successfully!<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Check the clinks<\/code> table to verify the deletion:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> clinks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Perl<\/figure>\n\n\n\n
Second, comment on the call to the\u00a0 delete_on_row()<\/code> subroutine, remove the comment of the call to the\u00a0 delete_all_rows()<\/code> subroutine, and execute the program:<\/p>\n\n\n
Perl MySQL Delete Data Demo\nAll links deleted successfully!<\/code><\/span><\/pre>\n\n\n
Query the clinks<\/code> table again to verify the truncate table operation:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> clinks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns no row as expected.<\/p>\n\n\n\n
In this tutorial, you have learned how to use Perl DBI to delete data in a table of a MySQL database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t