How to execute UPDATE queries with Perl DBI?

Version and Gotchas

• DBI is stable and consistent in Perl 5.8+ and later.
• Always check for undef returns to catch errors.
• Use RaiseError or manual error checking to handle failures gracefully.
• Remember that UPDATE returns the number of rows changed, which can be zero if no rows match.
• Beware of quoting issues—placeholders handle this safely.

Runnable Example

use strict;
use warnings;
use DBI;

# Example uses SQLite in-memory database for demonstration,
# since it requires no setup or external access.

my $dsn = "dbi:SQLite:dbname=:memory:";
my $user = "";
my $password = "";

# Connect with RaiseError to die on error automatically
my $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1, AutoCommit => 1 });

# Create test table
$dbh->do('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');

# Insert some initial data
$dbh->do('INSERT INTO users (name, age) VALUES (?, ?)', undef, 'Alice', 30);
$dbh->do('INSERT INTO users (name, age) VALUES (?, ?)', undef, 'Bob', 25);

# Now prepare an UPDATE statement with placeholders
my $sql = 'UPDATE users SET age = ? WHERE name = ?';
my $sth = $dbh->prepare($sql);

# Execute the UPDATE to change Bob's age to 26
my $rows_updated = $sth->execute(26, 'Bob');

print "Rows updated: $rows_updated\n";

# Verify update
my $new_age = $dbh->selectrow_array('SELECT age FROM users WHERE name = ?', undef, 'Bob');
print "Bob's new age: $new_age\n";

# Clean up
$sth->finish;
$dbh->disconnect;

Output:

Rows updated: 1
Bob's new age: 26

This example demonstrates a typical UPDATE use case:

• Connect to the database (SQLite in-memory for ease).
• Create table and insert data.
• Prepare and execute an UPDATE query with placeholders.
• Check how many rows were updated.
• Verify the change with a SELECT query.

Using prepare and execute is preferred for updates, especially if you run the same query many times with different values. For a quick, single update, $dbh->do($sql, undef, @bind_values) is a handy shortcut:

my $rows = $dbh->do('UPDATE users SET age = ? WHERE name = ?', undef, 27, 'Alice');
print "Updated $rows row(s)\n";

That said, prepared statements are better if you repeat the query multiple times, improving efficiency and safety.

Summary: With Perl DBI, execute UPDATE queries by preparing the statement with placeholders, then calling execute with the actual values. Always check the return value to see how many rows were affected, and leverage RaiseError or error handling to catch issues early.