Commit | Line | Data |
1ffc68a9 |
1 | ## Removed from UPDATE.pod: |
2 | |
3 | =head2 Update a row or rows using a column calculation |
4 | |
5 | -- Yet another pointless example |
6 | UPDATE users |
7 | SET username = username || '.uk' |
8 | WHERE id = 1; |
9 | |
10 | =over |
11 | |
12 | =item 1. Create a Schema object representing the database you are working with: |
13 | |
14 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
15 | |
16 | =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to fetch data from: |
17 | |
18 | my $fred_user = $schema->resultset('User')->find({ id => 1 }); |
19 | |
20 | The Row object has an B<update> method that will change the values on |
21 | the object, and send an UPDATE query to the database. |
22 | |
23 | =item 3. Call the B<update> method, passing it a hashref of new data: |
24 | |
25 | # this won't yet work, DBIC for now mandates the [ {} => $value ] format, the simple \[ $sql, $value1, $value2 ] will start being recognized later on |
26 | # the only documentation we currently have is this, if you can turn it into a DBIC pod-patch it will be freaking awesome |
27 | # https://github.com/dbsrgits/dbix-class/commit/0e773352 |
28 | $fred_user->update({ username => \['username || ?', '.uk'] }); |
29 | |
30 | ^^ you never got around to this |
31 | |
32 | # the DBIC syntax is a tad different from te thing above (i.e. we no longer encourage 'dummy' crap) |
33 | The \[ .. ] syntax here is described in L<SQL::Abstract> |
34 | documentation, used for passing bind parameters. |
35 | |
36 | |
37 | =back |
38 | |
39 | =head2 Update a row based on data in other tables |
40 | |
41 | -- Slightly less pointless example |
42 | UPDATE posts |
43 | SET title = user.username || title |
44 | JOIN users user ON user.id = posts.user_id; |
45 | |
46 | Joining two tables for an update is a similar sort of exercise to |
47 | joining them for a select query and using data from both. |
48 | |
49 | =over |
50 | |
51 | =item 1. Create a Schema object representing the database you are working with: |
52 | |
53 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
54 | |
55 | =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: |
56 | |
57 | my $posts = $schema->resultset('Post')->search( |
58 | {}, |
59 | { join => 'user' } |
60 | ); |
61 | |
62 | The B<join> key takes as an argument a nested structure of one or more relation names (see L<DBIx::Class::Manual::SQLHackers::CREATE>). |
63 | |
64 | =item 3. Call the B<update> method on the resultset to run the UPDATE statement: |
65 | |
66 | $posts->update({ 'me.title' => \[ 'user.username || me.title' ] }); |
67 | |
68 | ^^ I am 95% sure this won't actually work, please try it (ideally as a passing or failing test) |
69 | |
70 | =back |