|
1 | 1 | <!-- |
2 | | -$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.33 2005/10/12 23:19:22 tgl Exp $ |
| 2 | +$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.34 2006/01/19 23:09:42 momjian Exp $ |
3 | 3 | PostgreSQL documentation |
4 | 4 | --> |
5 | 5 |
|
@@ -205,14 +205,32 @@ UPDATE employees SET sales_count = sales_count + 1 FROM accounts |
205 | 205 | WHERE accounts.name = 'Acme Corporation' |
206 | 206 | AND employees.id = accounts.sales_person; |
207 | 207 | </programlisting> |
| 208 | + </para> |
208 | 209 |
|
| 210 | + <para> |
209 | 211 | Perform the same operation, using a sub-select in the |
210 | 212 | <literal>WHERE</literal> clause: |
211 | 213 | <programlisting> |
212 | 214 | UPDATE employees SET sales_count = sales_count + 1 WHERE id = |
213 | 215 | (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); |
214 | 216 | </programlisting> |
| 217 | + </para> |
215 | 218 |
|
| 219 | + <para> |
| 220 | + Now that all the papers are signed, update the most recently closed |
| 221 | + deal of the travelling salesperson who closed the Rocket Powered |
| 222 | + Skates deal with the Acme Corporation. |
| 223 | +<programlisting> |
| 224 | +UPDATE employees SET last_closed_deal = deal.id |
| 225 | + FROM accounts JOIN deals ON (account.id = deal.account_id) |
| 226 | + WHERE deal.employee_id = employees.id |
| 227 | + AND deal.name = 'Rocket Powered Skates' |
| 228 | + AND accounts.name = 'Acme Corporation' |
| 229 | + ORDER BY deal.signed_date DESC LIMIT 1; |
| 230 | +</programlisting> |
| 231 | + </para> |
| 232 | + |
| 233 | + <para> |
216 | 234 | Attempt to insert a new stock item along with the quantity of stock. If |
217 | 235 | the item already exists, instead update the stock count of the existing |
218 | 236 | item. To do this without failing the entire transaction, use savepoints. |
|