3 DBIx::Class::Manual::SQLHackers::UPDATE - DBIx::Class for SQL Hackers - UPDATE
7 =item L<Introduction|DBIx::Class::Manual::SQLHackers::Introduction>
9 =item L<CREATE|DBIx::Class::Manual::SQLHackers::CREATE>
11 =item L<INSERT|DBIx::Class::Manual::SQLHackers::INSERT>
13 =item L<SELECT|DBIx::Class::Manual::SQLHackers::SELECT>
17 =item L<DELETE|DBIx::Class::Manual::SQLHackers::DELETE>
19 =item L<BEGIN, COMMIT|DBIx::Class::Manual::SQLHackers::Transactions>
25 =head2 Delayed update versus direct update
27 Rows may be updated in one of two ways. You can create an object
28 representing an existing database table row, and hold it in your
29 programmes memory, passing it around from function to function,
30 changing its values, before actually updating the contents into the
31 database. This is a delayed update.
33 A direct update still involves fetching the existing row from the
34 database, but instead of storing new column values in the Row object,
35 the update method is called and passed the set of new values to store
38 NOTE: Running a direct update on a row object that already has changed
39 values, will *also* apply those values to the database. If values are
40 changed both on the object, and in the update method arguments, the
41 argument values take precedence.
43 =head2 Updating a row in memory
45 To create a Row object for delayed update (or other manipulations), first fetch it from the database as described in L<Simple SELECT|DBIx::Class::Manual::SQLHackers::SELECT/Simple SELECT>.
49 =item 1. Create a Schema object representing the database you are working with:
51 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
53 =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to update the contents of:
55 my $fred_user = $schema->resultset('User')->find({ id => 1 });
57 B<$fred_user>'s contents can now be changed using the accessor
58 methods created by B<add_columns>, back in
59 L<CREATE|DBIx::Class::Manual::SQLHackers::CREATE>. These are generally named
60 after the columns in the database, so to change fred's real name, use
61 the B<realname> method.
63 =item 3. Call the B<realname> accessor method on the $fred_user object:
65 $fred_user->realname("John Bloggs");
67 This value has not yet changed in the database, we can make the actual
68 update by calling *update:
70 =item 4. Update the set value(s) into the database:
76 =head2 Update a single row with simple values
79 SET username = 'new@email.address'
82 To update an existing row, first B<find> it using the methods shown in L<DBIx::Class::Manual::SQLHackers::SELECT/Simple SELECT, one row via the primary key> or L<DBIx::Class::Manual::SQLHackers::SELECT/Simple SELECT, one row via a unique key>, for example:
86 =item 1. Create a Schema object representing the database you are working with:
88 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
90 =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from:
92 my $fred_user = $schema->resultset('User')->find({ id => 1 });
94 The Row object has an B<update> method that will change the values on
95 the object, and send an UPDATE query to the database.
97 =item 3. Call the B<update> method, passing it a hashref of new data:
99 $fred_user->update({ username => 'new@email.address' });
103 See also: L</Direct update versus delayed update>.
105 =head2 Update multiple rows with simple values
107 -- Warning, pointless example!
109 SET dob = '2010-08-16'
110 WHERE realname LIKE 'jess%';
112 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.
116 =item 1. Create a Schema object representing the database you are working with:
118 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
120 =item 2. Call the B<search> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from:
122 my $user_search = $schema->resultset('User')->search(
123 { realname => { like => 'jess%' } }
126 =item 3. Call the B<update> method on the resultset to change the matching rows:
128 $user_search->update({ dob => '2010-08-16' });
132 =head2 Update a row or rows using a column calculation
134 -- Yet another pointless example
136 SET username = username || '.uk'
141 =item 1. Create a Schema object representing the database you are working with:
143 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
145 =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from:
147 my $fred_user = $schema->resultset('User')->find({ id => 1 });
149 The Row object has an B<update> method that will change the values on
150 the object, and send an UPDATE query to the database.
152 =item 3. Call the B<update> method, passing it a hashref of new data:
154 $fred_user->update({ username => \['username || ?', '.uk'] });
156 The \[ .. ] syntax here is described in L<SQL::Abstract>
157 documentation, used for passing bind parameters.
161 =head2 Update a row based on data in other tables
163 -- Slightly less pointless example
165 SET title = user.username || title
166 JOIN users user ON user.id = posts.user_id;
168 Joining two tables for an update is a similar sort of exercise to
169 joining them for a select query and using data from both.
173 =item 1. Create a Schema object representing the database you are working with:
175 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
177 =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:
179 my $posts = $schema->resultset('Post')->search(
184 The B<join> key takes as an argument a nested structure of one or more relation names (see L<DBIx::Class::Manual::SQLHackers::CREATE>).
186 =item 3. Call the B<update> method on the resultset to run the UPDATE statement:
188 $posts->update({ 'me.title' => \[ 'user.username || me.title' ] });
192 =head2 Update or create a row
194 -- MySQL non-standardness (and another silly example)
195 INSERT INTO users ( ... )
197 ON DUPLICATE KEY UPDATE password = 'newpass';
201 SELECT id, username, dob, realname, password
203 WHERE username = 'joebloggs';
206 SET id = ?, username = ?, dob = ?, realname = ?, password = ?;
209 DBIx::Class does not produce the non-standard MySQL "ON DUPLICATE KEY
210 UPDATE", instead it has a shortcut for combining *find* and *update*.
212 To avoid race conditions, this should be done in a transaction.
216 =item 1. Create a Schema object representing the database you are working with:
218 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
220 =item 2. Call the B<txn_do> method on the schema object, passing it a coderef to execute inside the transaction:
222 $schema->txn_do( sub {
224 =item 3. Call the B<update_or_create> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to update data in:
226 $schema->resultset('User')->update_or_create(
228 username => 'joebloggs',
230 realname = 'Joe Bloggs'
233 key => 'uniq_username'
237 =item 4. Close off the transaction / coderef:
243 A transaction is issued containing two statements, a B<SELECT> and then
244 either an B<INSERT> or an B<UPDATE> depending on the results.
246 Do not use this method if you definitely don't have either the primary
247 key, or a unique index value available. The B<find> method used under
248 the hood will probably not do what you expect. In this case, manually
249 run a separate B<search> method call to check for existance, and then