Recipe4.8.Modifying Records in a Table


Recipe 4.8. Modifying Records in a Table

Problem

You want to modify values for some or all rows in a table. For example, you might want to increase the salaries of everyone in department 20 by 10%. The following result set shows the DEPTNO, ENAME, and SAL for employees in that department:

  select deptno,ename,sal   from emp  where deptno = 20  order by 1,3 DEPTNO ENAME             SAL ------ ---------- ----------     20 SMITH             800     20 ADAMS            1100     20 JONES            2975     20 SCOTT            3000     20 FORD             3000 

You want to bump all the SAL values by 10%.

Solution

Use the UPDATE statement to modify existing rows in a database table. For example:

 1 update emp 2    set sal = sal*1.10 3  where deptno = 20 

Discussion

Use the UPDATE statement along with a WHERE clause to specify which rows to update; if you exclude a WHERE clause, then all rows are updated. The expression SAL*1.10 in this solution returns the salary increased by 10%.

When preparing for a mass update, you may wish to preview the results. You can do that by issuing a SELECT statement that includes the expressions you plan to put into your SET clauses. The following SELECT shows the result of a 10% salary increase:

  select deptno,        ename,        sal      as orig_sal,        sal*.10  as amt_to_add,        sal*1.10 as new_sal   from emp  where deptno=20  order by 1,5 DEPTNO ENAME  ORIG_SAL AMT_TO_ADD  NEW_SAL ------ ------ -------- ----------  -------     20 SMITH       800         80      880     20 ADAMS      1100        110     1210     20 JONES      2975        298     3273     20 SCOTT      3000        300     3300     20 FORD       3000        300     3300 

The salary increase is broken down into two columns: one to show the increase over the old salary, and the other to show the new salary.




SQL Cookbook
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
Year: 2005
Pages: 235

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net