Modifying Rows Using the UPDATE Statement
The UPDATE statement modifies rows in a table.
The UPDATE statement requires the table name,
an optional WHERE clause that specifies the rows to be changed and
a list of column names, along with their new values, specified using the SET clause.
One or more rows can be changed using one single UPDATE statement.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
select * from emp;
SQL>
SQL> UPDATE emp
2 SET ename = 'Orange'
3 WHERE empno= 2;
1 row updated.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
1 SMITH CLERK 800 20
2 Orange SALESMAN 1600 30
3 WARD SALESMAN 1250 30
4 JONES MANAGER 2975 20
5 MARTIN SALESMAN 1250 30
6 BLAKE MANAGER 2850 30
7 CLARK MANAGER 2850 10
8 SCOTT ANALYST 3000 20
9 KING PRESIDENT 3000 10
10 TURNER SALESMAN 1500 30
11 ADAMS CLERK 1500 20
11 rows selected.
SQL>
Change multiple rows and multiple columns in the same UPDATE statement.
SQL> select * from emp;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
1 SMITH CLERK 800 20
2 Orange SALESMAN 1600 30
3 WARD SALESMAN 1250 30
4 JONES MANAGER 2975 20
5 MARTIN SALESMAN 1250 30
6 BLAKE MANAGER 2850 30
7 CLARK MANAGER 2850 10
8 SCOTT ANALYST 3000 20
9 KING PRESIDENT 3000 10
10 TURNER SALESMAN 1500 30
11 ADAMS CLERK 1500 20
11 rows selected.
SQL> update emp SET sal = sal * 1.20, ename = LOWER(ename);
11 rows updated.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
1 smith CLERK 960 20
2 orange SALESMAN 1920 30
3 ward SALESMAN 1500 30
4 jones MANAGER 3570 20
5 martin SALESMAN 1500 30
6 blake MANAGER 3420 30
7 clark MANAGER 3420 10
8 scott ANALYST 3600 20
9 king PRESIDENT 3600 10
10 turner SALESMAN 1800 30
11 adams CLERK 1800 20
11 rows selected.
SQL>