Perl and PostgreSQL – Updating Data

In order to update data in a PostgreSQL database, the SQL ‘Update’ statement needs to be used. The following example updates the record in the ‘person’ table that was added in the example for inserting data.

Updating data follows the same pattern as inserting data. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the following SQL statement when it is executed. One of the parameters is for an ‘id’, so that only the specified record is updated, in this case, the record with an ‘id’ of five. The other parameters are for updating the ‘lastname’ and ‘title’ fields against the record. A confirmation message is displayed. The declaration and execution of the SQL statement is wrapped in an ‘eval-or-do’ block to catch any errors that may arise.

use strict;
use warnings;
use DBI;

# Database connection variable.
my $connect;

eval 
{

    # Connect to database.
    $connect = DBI->connect("DBI:Pg:dbname = demo; host = localhost; port = 5432", 
                            "DemoUN", "DemoPW", {RaiseError => 1});

} 
or do 
{

    # Message confirming unsuccessful database connection.
    print "Database connection unsuccessful.\n";

    # Stop program execution.
    exit(1);

};

eval
{

    # Query parameters.
    my $lastname = "Bloggs";
    my $title = "Mrs";
    my $id = 5;

    # Query text.
    my $sqlText = " \
         UPDATE person  \
         SET lastname = ?,  \
         title = ? \ 
         WHERE id = ? \
    ";

    # Prepare the query.
    my $sqlUpdate = $connect->prepare($sqlText);

    # Execute the query.
    $sqlUpdate->execute($lastname, $title, $id);

    # Confirm successful updating of person information.
    print "Person information updated successfully.\n";

    # Clean up.
    $sqlUpdate->finish();
    $connect->disconnect();

}
or do
{

    # Confirm error updating person information and exit.
    print "Error updating person information.\n";
    exit(1);

}

The contents of the ‘person’ table now looks as follows.

id	firstname	lastname	title	dob
1	Bob	Smith	Mr	1980-01-20
2	George	Jones	Mr	1997-12-15
3	Fred	Bloggs	Mr	1975-05-07
4	Alan	White	Mr	1989-03-20
5	Fiona	Bloggs	Mrs	1985-05-19

Perl and PostgreSQL – Updating Data

More