Rows may be updated in one of two ways. You can create an object representing an existing database table row, and hold it in your programmes memory, passing it around from function to function, changing its values, before actually updating the contents into the database. This is a delayed update.
A direct update still involves fetching the existing row from the database, but instead of storing new column values in the Row object, the update method is called and passed the set of new values to store in the table.
NOTE: Running a direct update on a row object that already has changed values, will also apply those values to the database. If values are changed both on the object, and in the update method arguments, the argument values take precedence.
To create a Row object for delayed update (or other manipulations), first fetch it from the database as described in Simple SELECT.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the find method on the resultset for the [Source] you wish to update the contents of:
my $fred_user = $schema->resultset('User')->find({ id => 1 });
$fred_user's contents can now be changed using the accessor methods created by add_columns, back in Database structure. These are generally named after the columns in the database, so to change fred's real name, use the realname method.
Call the realname accessor method on the $fred_user object:
$fred_user->realname("John Bloggs");
This value has not yet changed in the database, we can make the actual update by calling *update:
Update the set value(s) into the database:
$fred_user->update();
UPDATE users
SET username = 'new@email.address'
WHERE id = 1;
To update an existing row, first find it using the methods shown in Simple SELECT, one row via the primary key or Simple SELECT, one row via a unique key, for example:
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the find method on the resultset for the [Source] you wish to fetch data from:
my $fred_user = $schema->resultset('User')->find({ id => 1 });
The Row object has an update method that will change the values on the object, and send an UPDATE query to the database.
Call the update method, passing it a hashref of new data:
$fred_user->update({ username => 'new@email.address' });
-- Warning, pointless example!
UPDATE users
SET dob = '2010-08-16'
WHERE realname LIKE 'jess%';
To update a whole set of rows, or all of them, we first need to create a ResultSet object representing the query conditions that would be needed to select that same set of rows. We need to use search, then we use the update method on the ResultSet.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the search method on the resultset for the [Source] you wish to fetch data from:
my $user_search = $schema->resultset('User')->search(
{ realname => { like => 'jess%' } }
);
Call the update method on the resultset to change the matching rows:
$user_search->update({ dob => '2010-08-16' });
-- Yet another pointless example
UPDATE users
SET username = username || '.uk'
WHERE id = 1;
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the find method on the resultset for the [Source] you wish to fetch data from:
my $fred_user = $schema->resultset('User')->find({ id => 1 });
The Row object has an update method that will change the values on the object, and send an UPDATE query to the database.
Call the update method, passing it a hashref of new data:
$fred_user->update({ username => \['username || ?', '.uk'] });
The [ .. ] syntax here is described in SQL::Abstract documentation, used for passing bind parameters.
-- Slightly less pointless example
UPDATE posts
SET title = user.username || title
JOIN users user ON user.id = posts.user_id;
Joining two tables for an update is a similar sort of exercise to joining them for a select query and using data from both.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the search method on the resultset for the [Source] you wish to update data in, joining to the second table:
my $posts = $schema->resultset('Post')->search(
{},
{ join => 'user' }
);
The join key takes as an argument a nested structure of one or more relation names (see CREATEing tables).
Call the update method on the resultset to run the UPDATE statement:
$posts->update({ 'me.title' => \[ 'user.username || me.title' ] });
-- MySQL non-standardness (and another silly example)
INSERT INTO users ( ... )
VALUES ( ... )
ON DUPLICATE KEY UPDATE password = 'newpass';
-- OR:
BEGIN TRANSACTION;
SELECT id, username, dob, realname, password
FROM users
WHERE username = 'joebloggs';
UPDATE users
SET id = ?, username = ?, dob = ?, realname = ?, password = ?;
COMMIT;
DBIx::Class does not produce the non-standard MySQL "ON DUPLICATE KEY UPDATE", instead it has a shortcut for combining find and update.
To avoid race conditions, this should be done in a transaction.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the txn_do method on the schema object, passing it a coderef to execute inside the transaction:
$schema->txn_do( sub {
Call the update_or_create method on the resultset for the [Source] you wish to update data in:
$schema->resultset('User')->update_or_create(
{
username => 'joebloggs',
dob => '2010-09-10',
realname = 'Joe Bloggs'
},
{
key => 'uniq_username'
}
);
Close off the transaction / coderef:
} );
A transaction is issued containing two statements, a SELECT and then either an INSERT or an UPDATE depending on the results.
Do not use this method if you definitely don't have either the primary key, or a unique index value available. The find method used under the hood will probably not do what you expect. In this case, manually run a separate search method call to check for existance, and then call create.
Last modified: 2012-02-06T12:18:48