Perl MySQL Update Data

Summary: in this tutorial, we will show you step by step how to update data in MySQL table by using Perl DBI.

Perl MySQL update data steps

To update data in a table, you use the UPDATE statement. You use the following steps to update data in a MySQL table by using DBI:

First, connect to the MySQL database by using the  connect() method.

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

Next, construct an UPDATE statement and pass it to the  prepare() method of the database handle object to prepare a statement for execution.

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

Then, if you want to pass values from the Perl program to the UPDATE statement, you put the placeholders (?) in the statement and bind the corresponding parameter by using the bind_param() method of the statement handle object.

$sth->bind_param(1,$value);
$sth->bind_param(2,$id);Code language: Perl (perl)

After that, call the  execute() method of the statement handle object to execute the query.

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

Finally, disconnect from the MySQL database by using the  disconnect() method.

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

Perl MySQL update data example

The following example updates data in the links table with link id 2. Before updating the data, let’s examine the links table first:

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

See the following program:

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

use DBI;

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

say "Perl MySQL Update Data Demo";

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

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

# update statement
my $sql = "UPDATE links
           SET title = ?,
               url = ?, 
               target = ?
	   WHERE link_id = ?";

my $sth = $dbh->prepare($sql);

my $id = 2;
my $title = "Perl MySQL Update Data Tutorial";
my $url = "https://www.mysqltutorial.org/perl-mysql/perl-mysql-update/";
my $target = "_self";

# bind the corresponding parameter
$sth->bind_param(1,$title);
$sth->bind_param(2,$url);
$sth->bind_param(3,$target);
$sth->bind_param(4,$id);

# execute the query
$sth->execute();

say "The record has been updated successfully!";

$sth->finish();

# disconnect from the MySQL database
$dbh->disconnect();Code language: Perl (perl)

The output is of the program is:

Perl MySQL Update Data Demo
The record has been updated successfully!

Let’s query the links table again to verify the update.

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

The link record with ID 2 has been changed successfully.

In this tutorial, you have learned how to update data in MySQL table by using prepared statements in Perl DBI.