NAME

DBIx::Class::Manual::SQLHackers::UPDATE - DBIx::Class for SQL Hackers - UPDATE

Introduction

CREATE

INSERT

SELECT

UPDATE

DELETE

BEGIN, COMMIT

UPDATEing data

Single row delayed update versus direct 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 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.

Updating a row in memory

To create a Row object for delayed update (or other manipulations), first fetch it from the database as described in Simple SELECT.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the find method on the resultset for the ResultSource 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 CREATE. These are generally named after the columns in the database, so to change fred's real name, use the realname method.

3. 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*:

4. Update the set value(s) into the database:

$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 a single row with simple values

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:

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the find method on the resultset for the ResultSource you wish to fetch data from:

    my $fred_user = $schema->resultset('User')->find({ id => 1 });

The Row object has an B<update> method that will change the values on
the object, and send an UPDATE query to the database.

3. Call the update method, passing it a hashref of new data:

$fred_user->update({ username => 'new@email.address' }); 

See also: "Direct update versus delayed update".

Update multiple rows with simple values

-- 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.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset for the ResultSource you wish to fetch data from:

my $user_search = $schema->resultset('User')->search(
  { realname => { like => 'jess%' } }
);

3. Call the update method on the resultset to change the matching rows:

$user_search->update({ dob => '2010-08-16' });

Update or create a row

-- 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.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the txn_do method on the schema object, passing it a coderef to execute inside the transaction:

$schema->txn_do( sub {

3. Call the update_or_create method on the resultset for the ResultSource you wish to update data in:

$schema->resultset('User')->update_or_create(
  {
    username => 'joebloggs',
    dob => '2010-09-10',
    realname = 'Joe Bloggs'
  },
  {
    key => 'uniq_username'
  }
);

4. 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.