SQL Server : 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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table