=head1 UPDATEing data
-=head2 Delayed update versus direct update
+=head2 Single row delayed update versus direct update
-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.
+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,
my $fred_user = $schema->resultset('User')->find({ id => 1 });
- B<$fred_user>'s contents can now be changed using the accessor
+B<$fred_user>'s contents can now be changed using the accessor
methods created by B<add_columns>, back in
L<CREATE|DBIx::Class::Manual::SQLHackers::CREATE>. These are generally named
after the columns in the database, so to change fred's real name, use
$fred_user->realname("John Bloggs");
- This value has not yet changed in the database, we can make the actual
-update by calling *update:
+
+This value has not yet changed in the database, we can make the actual
+update by calling *update*:
=item 4. Update the set value(s) into the database:
=back
+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. B<is_changed> returns true (or a list of changed
+column names), if any column values have changed. B<is_column_changed>
+will return true or false for the given column name argument. The
+previous values of the columns are not stored.
+
+
=head2 Update a single row with simple values
UPDATE users
=back
-=head2 Update a row or rows using a column calculation
-
- -- Yet another pointless example
- UPDATE users
- SET username = username || '.uk'
- WHERE id = 1;
-
-=over
-
-=item 1. Create a Schema object representing the database you are working with:
-
- my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
-
-=item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::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.
-
-=item 3. Call the B<update> method, passing it a hashref of new data:
-
- $fred_user->update({ username => \['username || ?', '.uk'] });
-
-The \[ .. ] syntax here is described in L<SQL::Abstract>
-documentation, used for passing bind parameters.
-
-=back
-
-=head2 Update a row based on data in other tables
-
- -- 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.
-
-=over
-
-=item 1. Create a Schema object representing the database you are working with:
-
- my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
-
-=item 2. Call the B<search> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to update data in, joining to the second table:
-
- my $posts = $schema->resultset('Post')->search(
- {},
- { join => 'user' }
- );
-
- The B<join> key takes as an argument a nested structure of one or more relation names (see L<DBIx::Class::Manual::SQLHackers::CREATE>).
-
-=item 3. Call the B<update> method on the resultset to run the UPDATE statement:
-
- $posts->update({ 'me.title' => \[ 'user.username || me.title' ] });
-
-=back
-
=head2 Update or create a row
-- MySQL non-standardness (and another silly example)
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*.
+DBIx::Class does not yet produce the non-standard MySQL "ON DUPLICATE KEY
+UPDATE", instead it has a shortcut for combining B<find> and B<update>.
To avoid race conditions, this should be done in a transaction.
projects / dbsrgits/DBIx-Class-Manual-SQLHackers.git / blobdiff