Commit | Line | Data |
6c2a4396 |
1 | =head1 NAME |
2 | |
3 | DBIx::Class::Manual::SQLHackers::UPDATE - DBIx::Class for SQL Hackers - UPDATE |
4 | |
5 | =over |
6 | |
7 | =item L<Introduction|DBIx::Class::Manual::SQLHackers::Introduction> |
8 | |
9 | =item L<CREATE|DBIx::Class::Manual::SQLHackers::CREATE> |
10 | |
11 | =item L<INSERT|DBIx::Class::Manual::SQLHackers::INSERT> |
12 | |
13 | =item L<SELECT|DBIx::Class::Manual::SQLHackers::SELECT> |
14 | |
15 | =item UPDATE |
16 | |
17 | =item L<DELETE|DBIx::Class::Manual::SQLHackers::DELETE> |
18 | |
19 | =item L<BEGIN, COMMIT|DBIx::Class::Manual::SQLHackers::Transactions> |
20 | |
21 | =back |
22 | |
23 | =head1 UPDATEing data |
24 | |
25 | =head2 Delayed update versus direct update |
26 | |
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. |
32 | |
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 |
36 | in the table. |
37 | |
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. |
42 | |
43 | =head2 Updating a row in memory |
44 | |
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>. |
46 | |
47 | =over |
48 | |
49 | =item 1. Create a Schema object representing the database you are working with: |
50 | |
51 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
52 | |
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: |
54 | |
55 | my $fred_user = $schema->resultset('User')->find({ id => 1 }); |
56 | |
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. |
62 | |
63 | =item 3. Call the B<realname> accessor method on the $fred_user object: |
64 | |
65 | $fred_user->realname("John Bloggs"); |
66 | |
67 | This value has not yet changed in the database, we can make the actual |
68 | update by calling *update: |
69 | |
70 | =item 4. Update the set value(s) into the database: |
71 | |
72 | $fred_user->update(); |
73 | |
74 | =back |
75 | |
76 | =head2 Update a single row with simple values |
77 | |
78 | UPDATE users |
79 | SET username = 'new@email.address' |
80 | WHERE id = 1; |
81 | |
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: |
83 | |
84 | =over |
85 | |
86 | =item 1. Create a Schema object representing the database you are working with: |
87 | |
88 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
89 | |
90 | =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from: |
91 | |
92 | my $fred_user = $schema->resultset('User')->find({ id => 1 }); |
93 | |
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. |
96 | |
97 | =item 3. Call the B<update> method, passing it a hashref of new data: |
98 | |
99 | $fred_user->update({ username => 'new@email.address' }); |
100 | |
101 | =back |
102 | |
103 | See also: L</Direct update versus delayed update>. |
104 | |
105 | =head2 Update multiple rows with simple values |
106 | |
107 | -- Warning, pointless example! |
108 | UPDATE users |
109 | SET dob = '2010-08-16' |
110 | WHERE realname LIKE 'jess%'; |
111 | |
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. |
113 | |
114 | =over |
115 | |
116 | =item 1. Create a Schema object representing the database you are working with: |
117 | |
118 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
119 | |
120 | =item 2. Call the B<search> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from: |
121 | |
122 | my $user_search = $schema->resultset('User')->search( |
123 | { realname => { like => 'jess%' } } |
124 | ); |
125 | |
126 | =item 3. Call the B<update> method on the resultset to change the matching rows: |
127 | |
128 | $user_search->update({ dob => '2010-08-16' }); |
129 | |
130 | =back |
131 | |
132 | =head2 Update a row or rows using a column calculation |
133 | |
134 | -- Yet another pointless example |
135 | UPDATE users |
136 | SET username = username || '.uk' |
137 | WHERE id = 1; |
138 | |
139 | =over |
140 | |
141 | =item 1. Create a Schema object representing the database you are working with: |
142 | |
143 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
144 | |
145 | =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from: |
146 | |
147 | my $fred_user = $schema->resultset('User')->find({ id => 1 }); |
148 | |
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. |
151 | |
152 | =item 3. Call the B<update> method, passing it a hashref of new data: |
153 | |
154 | $fred_user->update({ username => \['username || ?', '.uk'] }); |
155 | |
156 | The \[ .. ] syntax here is described in L<SQL::Abstract> |
157 | documentation, used for passing bind parameters. |
158 | |
159 | =back |
160 | |
161 | =head2 Update a row based on data in other tables |
162 | |
163 | -- Slightly less pointless example |
164 | UPDATE posts |
165 | SET title = user.username || title |
166 | JOIN users user ON user.id = posts.user_id; |
167 | |
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. |
170 | |
171 | =over |
172 | |
173 | =item 1. Create a Schema object representing the database you are working with: |
174 | |
175 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
176 | |
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: |
178 | |
179 | my $posts = $schema->resultset('Post')->search( |
180 | {}, |
181 | { join => 'user' } |
182 | ); |
183 | |
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>). |
185 | |
186 | =item 3. Call the B<update> method on the resultset to run the UPDATE statement: |
187 | |
188 | $posts->update({ 'me.title' => \[ 'user.username || me.title' ] }); |
189 | |
190 | =back |
191 | |
192 | =head2 Update or create a row |
193 | |
194 | -- MySQL non-standardness (and another silly example) |
195 | INSERT INTO users ( ... ) |
196 | VALUES ( ... ) |
197 | ON DUPLICATE KEY UPDATE password = 'newpass'; |
198 | |
199 | -- OR: |
200 | BEGIN TRANSACTION; |
201 | SELECT id, username, dob, realname, password |
202 | FROM users |
203 | WHERE username = 'joebloggs'; |
204 | |
205 | UPDATE users |
206 | SET id = ?, username = ?, dob = ?, realname = ?, password = ?; |
207 | COMMIT; |
208 | |
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*. |
211 | |
212 | To avoid race conditions, this should be done in a transaction. |
213 | |
214 | =over |
215 | |
216 | =item 1. Create a Schema object representing the database you are working with: |
217 | |
218 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
219 | |
220 | =item 2. Call the B<txn_do> method on the schema object, passing it a coderef to execute inside the transaction: |
221 | |
222 | $schema->txn_do( sub { |
223 | |
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: |
225 | |
226 | $schema->resultset('User')->update_or_create( |
227 | { |
228 | username => 'joebloggs', |
229 | dob => '2010-09-10', |
230 | realname = 'Joe Bloggs' |
231 | }, |
232 | { |
233 | key => 'uniq_username' |
234 | } |
235 | ); |
236 | |
237 | =item 4. Close off the transaction / coderef: |
238 | |
239 | } ); |
240 | |
241 | =back |
242 | |
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. |
245 | |
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 |
250 | call B<create>. |
251 | |