See also: L</Direct update versus delayed update>.
-=head2 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 B<search>, then we use the B<update> method on the ResultSet.
-
-=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 fetch data from:
-
- my $user_search = $schema->resultset('User')->search(
- { realname => { like => 'jess%' } }
- );
-
-=item 3. Call the B<update> method on the resultset to change the matching rows:
-
- $user_search->update({ dob => '2010-08-16' });
-
-=back
+## Removed from UPDATE.pod:
=head2 Update a row or rows using a column calculation
=item 3. Call the B<update> method, passing it a hashref of new data:
-# this won't yet work, DBIC for now mandates the [ {} => $value ] format, the simple \[ $sql, $value1, $value2 ] will start being recognized later on
-# the only documentation we currently have is this, if you can turn it into a DBIC pod-patch it will be freaking awesome
-# https://github.com/dbsrgits/dbix-class/commit/0e773352
- $fred_user->update({ username => \['username || ?', '.uk'] });
-
-# the DBIC syntax is a tad different from te thing above (i.e. we no longer encourage 'dummy' crap)
-The \[ .. ] syntax here is described in L<SQL::Abstract>
-documentation, used for passing bind parameters.
+ $fred_user->update({ username => \['username || ?', [ {} => '.uk'] ] });
+The \[ .. ] syntax here is base on L<SQL::Abstract/Literal SQL with placeholders and bind values (subqueries)>, 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.
=back
-=head2 Update a row based on data in other tables
+=head2 Update multiple rows with simple values
- -- Slightly less pointless example
- UPDATE posts
- SET title = user.username || title
- JOIN users user ON user.id = posts.user_id;
+ -- Warning, pointless example!
+ UPDATE users
+ SET dob = '2010-08-16'
+ WHERE realname LIKE 'jess%';
-Joining two tables for an update is a similar sort of exercise to
-joining them for a select query and using data from both.
+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 B<search>, then we use the B<update> method on the ResultSet.
=over
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:
+=item 2. Call the B<search> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from:
- my $posts = $schema->resultset('Post')->search(
- {},
- { join => 'user' }
+ my $user_search = $schema->resultset('User')->search(
+ { realname => { like => 'jess%' } }
);
- 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:
+=item 3. Call the B<update> method on the resultset to change the matching rows:
- $posts->update({ 'me.title' => \[ 'user.username || me.title' ] });
+ $user_search->update({ dob => '2010-08-16' });
=back
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