{"id":3017,"date":"2013-01-17T19:44:02","date_gmt":"2013-01-18T03:44:02","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3017"},"modified":"2024-01-04T01:27:12","modified_gmt":"2024-01-04T08:27:12","slug":"perl-mysql-update-data","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/perl-mysql\/perl-mysql-update-data\/","title":{"rendered":"Perl MySQL Update Data"},"content":{"rendered":"\n

Summary:<\/strong> in this tutorial, we will show you step by step how to update data in MySQL table by using Perl DBI.<\/p>\n\n\n\n

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

To update data in a table, you use the UPDATE statement<\/a>. You use the following steps to update data in a MySQL table by using DBI:<\/p>\n\n\n\n

First, connect to the MySQL database<\/a> by using the  connect()<\/code> method.<\/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, construct an UPDATE<\/code> statement and pass it to the  prepare()<\/code> method of the database handle object to prepare a statement for execution.<\/p>\n\n\n
my<\/span> $sql = \"UPDATE table_name SET col_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, if you want to pass values from the Perl program to the UPDATE<\/code> statement, you put the placeholders (?) in the statement and bind the corresponding parameter by using the bind_param()<\/code> method of the statement handle object.<\/p>\n\n\n
$sth->bind_param(1<\/span>,$value);\n$sth->bind_param(2<\/span>,$id);<\/code><\/span>Code language:<\/span> Perl<\/span> (<\/span>perl<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, call the  execute()<\/code> method of the statement handle object to execute the query.<\/p>\n\n\n
$sth->execute();<\/code><\/span>Code language:<\/span> Perl<\/span> (<\/span>perl<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, disconnect from the MySQL database by using the  disconnect()<\/code> method.<\/p>\n\n\n
$dbh->disconnect();<\/code><\/span>Code language:<\/span> Perl<\/span> (<\/span>perl<\/span>)<\/span><\/small><\/pre>\n\n\n
Perl MySQL update data example<\/h2>\n\n\n\n
The following example updates data in the links table with link id 2. Before updating the data, let’s examine the links table first:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> links;<\/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
See the following program:<\/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\n# MySQL database configurations<\/span>\nmy<\/span> $dsn = \"DBI:mysql:perlmysqldb\"<\/span>;\nmy<\/span> $username = \"root\"<\/span>;\nmy<\/span> $password = ''<\/span>;\n\nsay<\/span> \"Perl MySQL Update Data Demo\"<\/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# update statement<\/span>\nmy<\/span> $sql = \"UPDATE links\n           SET title = ?,\n               url = ?, \n               target = ?\n\t   WHERE link_id = ?\"<\/span>;\n\nmy<\/span> $sth = $dbh->prepare($sql);\n\nmy<\/span> $id = 2<\/span>;\nmy<\/span> $title = \"Perl MySQL Update Data Tutorial\"<\/span>;\nmy<\/span> $url = \"https:\/\/www.mysqltutorial.org\/perl-mysql\/perl-mysql-update\/\"<\/span>;\nmy<\/span> $target = \"_self\"<\/span>;\n\n# bind the corresponding parameter<\/span>\n$sth->bind_param(1<\/span>,$title);\n$sth->bind_param(2<\/span>,$url);\n$sth->bind_param(3<\/span>,$target);\n$sth->bind_param(4<\/span>,$id);\n\n# execute the query<\/span>\n$sth->execute();\n\nsay<\/span> \"The record has been updated successfully!\"<\/span>;\n\n$sth->finish();\n\n# disconnect from the MySQL database<\/span>\n$dbh->disconnect();<\/code><\/span>Code language:<\/span> Perl<\/span> (<\/span>perl<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is of the program is:<\/p>\n\n\n
Perl MySQL Update Data Demo\nThe record has been updated successfully!<\/code><\/span><\/pre>\n\n\n
Let’s query the links<\/code> table again to verify the update.<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> links;<\/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 link record with ID 2 has been changed successfully.<\/p>\n\n\n\n
In this tutorial, you have learned how to update data in MySQL table by using prepared statements in Perl DBI.<\/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