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 Single row delayed update versus direct update
27 Individual rows may be updated via their Result object in one of two
28 ways. You can create an object representing an existing database table
29 row and hold it in your program's memory, passing it around from
30 function to function changing its values, before actually updating
31 the contents into the 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 B<also> apply those values to the database. If some 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, one row via the primary key>.
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");
68 This value has not yet changed in the database, we can make the actual
69 update by calling B<update>:
71 =item 4. Write the new value(s) into the database:
77 The update method will only actually send an UPDATE statement to the
78 database if one or more of the columns have changed. The internal
79 tracking of which columns have been changed can be queried using
80 several methods. B<is_changed> returns true (or a list of changed
81 column names) if any column values have changed. B<is_column_changed>
82 will return true or false for the given column name argument. The
83 previous values of the columns are not stored.
86 =head2 Update a single row with simple values
89 SET username = 'new@email.address'
92 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:
96 =item 1. Create a Schema object representing the database you are working with:
98 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
100 =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from:
102 my $fred_user = $schema->resultset('User')->find({ id => 1 });
104 The Row object has an B<update> method that will change the values on
105 the object and send an UPDATE query to the database.
107 =item 3. Call the B<update> method, passing it a hashref of new data:
109 $fred_user->update({ username => 'new@email.address' });
113 See also: L</Single row direct update versus delayed update>.
115 =head2 Update a row or rows using a column calculation
117 -- Yet another pointless example
119 SET username = username || '.uk'
124 =item 1. Create a Schema object representing the database you are working with:
126 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
128 =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from:
130 my $fred_user = $schema->resultset('User')->find({ id => 1 });
132 The Row object has an B<update> method that will change the values on
133 the object, and send an UPDATE query to the database.
135 =item 3. Call the B<update> method, passing it a hashref of new data:
137 $fred_user->update({ username => \['username || ?', [ {} => '.uk'] ] });
139 The C<\[ ... ]> syntax here is based 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.
143 =head2 Update multiple rows with simple values
145 -- Warning, pointless example!
147 SET dob = '2010-08-16'
148 WHERE realname LIKE 'jess%';
150 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.
154 =item 1. Create a Schema object representing the database you are working with:
156 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
158 =item 2. Call the B<search> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from:
160 my $user_search = $schema->resultset('User')->search(
161 { realname => { like => 'jess%' } }
164 =item 3. Call the B<update> method on the resultset to change the matching rows:
166 $user_search->update({ dob => '2010-08-16' });
170 =head2 Update or create a row
172 -- MySQL non-standardness (and another silly example)
173 INSERT INTO users ( ... )
175 ON DUPLICATE KEY UPDATE password = 'newpass';
179 SELECT id, username, dob, realname, password
181 WHERE username = 'joebloggs';
184 SET id = ?, username = ?, dob = ?, realname = ?, password = ?;
187 DBIx::Class does not yet produce the non-standard MySQL "ON DUPLICATE KEY
188 UPDATE", instead it has a shortcut for combining B<find> and B<update>.
190 To avoid race conditions, this should be done in a transaction.
194 =item 1. Create a Schema object representing the database you are working with:
196 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
198 =item 2. Call the B<txn_do> method on the schema object, passing it a coderef to execute inside the transaction:
200 $schema->txn_do( sub {
202 =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:
204 $schema->resultset('User')->update_or_create(
206 username => 'joebloggs',
208 realname = 'Joe Bloggs'
211 key => 'uniq_username'
215 =item 4. Close off the coderef / transaction:
221 A transaction is issued containing two statements, a B<SELECT> and then
222 either an B<INSERT> or an B<UPDATE> depending on the results.
224 Do not use this method if you don't definitely have either the primary
225 key or a unique index value available. The B<find> method used under
226 the hood will probably not do what you expect. In this case, manually
227 run a separate B<search> method call to check for existence, and then