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 | |
abc32120 |
25 | =head2 Single row delayed update versus direct update |
6c2a4396 |
26 | |
abc32120 |
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 programmes 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. |
6c2a4396 |
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 | |
abc32120 |
57 | B<$fred_user>'s contents can now be changed using the accessor |
6c2a4396 |
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 | |
abc32120 |
67 | |
68 | This value has not yet changed in the database, we can make the actual |
69 | update by calling *update*: |
6c2a4396 |
70 | |
71 | =item 4. Update the set value(s) into the database: |
72 | |
73 | $fred_user->update(); |
74 | |
75 | =back |
76 | |
abc32120 |
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. |
84 | |
85 | |
6c2a4396 |
86 | =head2 Update a single row with simple values |
87 | |
88 | UPDATE users |
89 | SET username = 'new@email.address' |
90 | WHERE id = 1; |
91 | |
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: |
93 | |
94 | =over |
95 | |
96 | =item 1. Create a Schema object representing the database you are working with: |
97 | |
98 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
99 | |
100 | =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from: |
101 | |
102 | my $fred_user = $schema->resultset('User')->find({ id => 1 }); |
103 | |
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. |
106 | |
107 | =item 3. Call the B<update> method, passing it a hashref of new data: |
108 | |
109 | $fred_user->update({ username => 'new@email.address' }); |
110 | |
111 | =back |
112 | |
113 | See also: L</Direct update versus delayed update>. |
114 | |
115 | =head2 Update multiple rows with simple values |
116 | |
117 | -- Warning, pointless example! |
118 | UPDATE users |
119 | SET dob = '2010-08-16' |
120 | WHERE realname LIKE 'jess%'; |
121 | |
122 | 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. |
123 | |
124 | =over |
125 | |
126 | =item 1. Create a Schema object representing the database you are working with: |
127 | |
128 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
129 | |
130 | =item 2. Call the B<search> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from: |
131 | |
132 | my $user_search = $schema->resultset('User')->search( |
133 | { realname => { like => 'jess%' } } |
134 | ); |
135 | |
136 | =item 3. Call the B<update> method on the resultset to change the matching rows: |
137 | |
138 | $user_search->update({ dob => '2010-08-16' }); |
139 | |
140 | =back |
141 | |
6c2a4396 |
142 | =head2 Update or create a row |
143 | |
144 | -- MySQL non-standardness (and another silly example) |
145 | INSERT INTO users ( ... ) |
146 | VALUES ( ... ) |
147 | ON DUPLICATE KEY UPDATE password = 'newpass'; |
148 | |
149 | -- OR: |
150 | BEGIN TRANSACTION; |
151 | SELECT id, username, dob, realname, password |
152 | FROM users |
153 | WHERE username = 'joebloggs'; |
154 | |
155 | UPDATE users |
156 | SET id = ?, username = ?, dob = ?, realname = ?, password = ?; |
157 | COMMIT; |
158 | |
2f41b1a9 |
159 | DBIx::Class does not yet produce the non-standard MySQL "ON DUPLICATE KEY |
0e8be732 |
160 | UPDATE", instead it has a shortcut for combining B<find> and B<update>. |
6c2a4396 |
161 | |
162 | To avoid race conditions, this should be done in a transaction. |
163 | |
164 | =over |
165 | |
166 | =item 1. Create a Schema object representing the database you are working with: |
167 | |
168 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
169 | |
170 | =item 2. Call the B<txn_do> method on the schema object, passing it a coderef to execute inside the transaction: |
0e8be732 |
171 | |
6c2a4396 |
172 | $schema->txn_do( sub { |
173 | |
174 | =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: |
175 | |
176 | $schema->resultset('User')->update_or_create( |
177 | { |
178 | username => 'joebloggs', |
179 | dob => '2010-09-10', |
180 | realname = 'Joe Bloggs' |
181 | }, |
182 | { |
183 | key => 'uniq_username' |
184 | } |
185 | ); |
186 | |
187 | =item 4. Close off the transaction / coderef: |
188 | |
189 | } ); |
190 | |
191 | =back |
192 | |
193 | A transaction is issued containing two statements, a B<SELECT> and then |
194 | either an B<INSERT> or an B<UPDATE> depending on the results. |
195 | |
196 | Do not use this method if you definitely don't have either the primary |
197 | key, or a unique index value available. The B<find> method used under |
198 | the hood will probably not do what you expect. In this case, manually |
199 | run a separate B<search> method call to check for existance, and then |
200 | call B<create>. |
201 | |