Perl MySQL: How to Update MySQL table using Perl

By /
Perl Tutorial How To Update MySQL Table Using Perl

In this tutorial, we’ll learn to update the MySQL table with several examples using the Perl programming language.

Also read: Perl Tutorial: How to create a table in MySQL using Perl

Pre-requisites

There are three pre-requisites for this tutorial to be installed on your system:

  1. MySQL: MySQL (server, workbench, command line) must be installed on your system, download it from the below link:
    https://dev.mysql.com/downloads/
  2. Perl: Perl must be installed on your system, download from the mentioned link if not installed yet:
    https://www.perl.org/get.html
  3. DBD MySQL driver: We must download

How to Update a MySQL table using Perl

There are 4 steps to connect to a database and update the table in MySQL using Perl programming language, in this section, we will understand them one by one:

Establish a Connection

To establish a connection we need to connect MySQL to Perl, for that we need three attributes for this connection:

  1. Database Name
  2. User Name
  3. Password

Let’s see the code for connection, using all the attributes:

$myconn = DBI->connect('DBI:mysql:databaseName','username','password');Code language: SQL (Structured Query Language) (sql)

Write the UPDATE Query

In SQL, UPDATE is a DML (Data Manipulation Language) command. Used for Manipulating Data in tables, let’s see the UPDATE statement:

UPDATE tableName SET field1 = 'value' WHERE field1 = 'value';Code language: SQL (Structured Query Language) (sql)

Execute the Query

After connecting Perl with MySQL, we will write the update statement and execute the statement using the do() method.

$myconn->do("UPDATE tableName SET Query");Code language: SQL (Structured Query Language) (sql)

Close Connection

In the end, we will close the connection using the disconnect() method, let’s see the query:

$myconn->disconnect();Code language: SQL (Structured Query Language) (sql)

A Few Examples With Detailed Explanation

The first example, will the when we want to UPDATE a record in the employee table, where empid = 1, let’s see the full code below:

use DBI;

#Create connection
$myconn = DBI->connect('DBI:mysql:db','sam','root');

#Write SQL query and execute
$myconn->do("UPDATE employee SET fname = 'Mick' WHERE empid = 1");

#disconnect connection
$myconn->disconnect();Code language: SQL (Structured Query Language) (sql)

Let’s execute the code, for that open Command Prompt, and change the directory where you have created a .pl file. Let’s execute the below command:

perl filename.plCode language: CSS (css)

Now let’s check whether the updating is successful or not, for that, we’ll open the command prompt and login to the MySQL account:

mysql -u username -pCode language: Bash (bash)

After logging in, let’s go to the database where our employee table resides, in our case, DB is the database where the employee table is:

USE db;Code language: SQL (Structured Query Language) (sql)

Let’s view the table details, using the SELECT statement:

SELECT * FROM employee;
Update Mysql Database Using Perl

Let’s see another example, where we will update the first name and last name of the employee whose empid is 3. Below is the code for the same, save the file with the .pl extension.

use DBI;

#Create connection
$myconn = DBI->connect('DBI:mysql:db','sam','root');

#Write SQL query and execute
$myconn->do("UPDATE employee SET fname = 'Nina',lname = 'Posner' WHERE empid = 3");

#disconnect connection
$myconn->disconnect();Code language: SQL (Structured Query Language) (sql)

Let’s execute the above code, open the Command Prompt, and change the directory to the one in which you’ve created the .pl file and write the below command:

perl filename.plCode language: Bash (bash)

After executing the command, let’s check the table if it’s updated or not. For that, you’ll have to either open the MySQL command line or open a command prompt to open the MySQL command line. Write below SQL command after opening MySQL console:

SELECT * FROM employee;Code language: SQL (Structured Query Language) (sql)
MySQL Perl Update

In the above example, you can see that the employee with empid as 3, first name, and last name has been updated.

Conclusion

In this tutorial we learned how to connect to MySQL using Perl, we learned all the steps from connection to disconnecting from the database, we learned how to update a MySQL table records using Perl programming language, and in the end, we saw a few examples to do the same. This was a simple guide to updating MySQL tables using Perl.