How to modify records in a table?

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

If you want to modify values for some or all rows in a table.For instance, you might wish to give everyone in department 20 a 10% pay raise. The deptno, ename, and sal for employees in that department are displayed in the result set below:

SELECT deptno,ename,sal
FROM Staff
WHERE deptno = 20
ORDER BY 1,3

Output

deptno ename sal
20 ADAM 1100.00
20 JONES 2975.00
20 RAHUL 3000.00
20 ARHAM 3000.00
20 ALI 800.00

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

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

UPDATE Staff
SET sal = sal*1.10
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%.

You might want to preview the outcomes when getting ready for a large upgrade. This can be accomplished by executing a SELECT statement that contains the expressions that will be included in your SET clauses. The result of a 10% pay rise is shown in the following SELECT:

SELECT deptno,ename,
sal as orig_sal, 
sal*.10 as amt_to_add,
sal*1.10 as new_sal
FROM Staff
WHERE deptno=20
ORDER BY 1,5

Output

deptno ename orig_sal amt_to_add new_sal
20 ALI 800.00 80.0 880.0
20 ADAM 1100.00 110.0 1210.0
20 JONES 2975.00 297.5 3272.5
20 RAHUL 3000.00 300.0 3300.0
20 ARHAM 3000.00 300.0 3300.0

Two columns have been created to display the new pay and the rise from the previous one in the salary.