DBIx::Class::Manual::SQLHackers::UPDATE - DBIx::Class for SQL Hackers - UPDATE
Individual rows may be updated via their Result object in one of two ways. You can create an object representing an existing database table row and hold it in your program's 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 some 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.
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
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 CREATE. These are generally named after the columns in the database, so to change fred's real name, use the realname method.
$fred_user->realname("John Bloggs");
This value has not yet changed in the database, we can make the actual update by calling update:
$fred_user->update();
The update method will only actually send an UPDATE statement to the database if one or more of the columns have changed. The internal tracking of which columns have been changed can be queried using several methods. is_changed returns true (or a list of changed column names) if any column values have changed. is_column_changed will return true or false for the given column name argument. The previous values of the columns are not stored.
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" in DBIx::Class::Manual::SQLHackers::SELECT or "Simple SELECT, one row via a unique key" in DBIx::Class::Manual::SQLHackers::SELECT, for example:
The Row object has an update method that will change the values on the object and send an UPDATE query to the database.
$fred_user->update({ username => 'new@email.address' });
See also: "Single row direct update versus delayed update".
-- Yet another pointless example UPDATE users SET username = username || '.uk' WHERE 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.
$fred_user->update({ username => \['username || ?', [ {} => '.uk'] ] });
The \[ ... ] syntax here is based on "Literal SQL with placeholders and bind values (subqueries)" in SQL::Abstract, and adds some extra syntax for the "values" to be able to supply things like the exact SQL bind type and so on. This extra syntax will be documented in DBIx::Class soon.
\[ ... ]
-- 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.
my $user_search = $schema->resultset('User')->search( { realname => { like => 'jess%' } } );
$user_search->update({ dob => '2010-08-16' });
-- 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 yet 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.
$schema->txn_do( sub {
$schema->resultset('User')->update_or_create( { username => 'joebloggs', dob => '2010-09-10', realname = 'Joe Bloggs' }, { key => 'uniq_username' } );
} );
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 don't definitely 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 existence, and then call create.
To install DBIx::Class::Manual::SQLHackers::UPDATE, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Class::Manual::SQLHackers::UPDATE
CPAN shell
perl -MCPAN -e shell install DBIx::Class::Manual::SQLHackers::UPDATE
For more information on module installation, please visit the detailed CPAN module installation guide.