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 change the values of some or all rows in a table, you can use the UPDATE statement. For example, if you want to give a pay raise to all employees in department 20, you can use the following steps:
- Preview the changes by running a SELECT statement that displays the deptno, ename, and sal for employees in department 20, sorted by deptno and sal.
- Use the UPDATE statement to modify the salary of all employees in department 20 by 10%. Make sure to specify a WHERE clause to ensure that only the rows you want to update are affected.
- Test the changes by running a SELECT statement that displays the original salary, the raise amount, and the new salary for employees in department 20. This will allow you to see the results of the pay raise before committing to it.
- Once you are satisfied with the changes, execute the UPDATE statement to apply the pay raise to all employees in department 20.
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 |
To increase the value of the sal column for all rows in a table, you can use the UPDATE statement. For example:
UPDATE Staff
SET sal = sal*1.10
WHERE deptno = 20
Discussion
To update specific rows in a table, you can use the UPDATE statement with a WHERE clause to specify which rows to modify. If you omit the WHERE clause, all rows will be updated. The expression “sal * 1.10” will increase the salary by 10%.
Before making a large update, it is a good idea to preview the results. You can do this by executing a SELECT statement that includes the expressions that will be used in the SET clause of the UPDATE statement. This will allow you to see the outcome of the update before applying it. For example, you can run the following SELECT statement to see the result of a 10% pay increase:
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 |
In the SELECT statement, two additional columns have been created to show the new salary after the pay increase, as well as the amount of the raise. These columns display the results of the salary update and allow you to see the new pay and the increase from the previous salary for each employee.