Recipe 4.8. Modifying Records in a TableProblemYou 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%. SolutionUse 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 DiscussionUse 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. |