Sayantan's Blog On Oracle

Updating data in Oracle

Update operation is used to modify data in oracle.

Types of Update operation:

• SINGLE ROW UPDATE
• MULTIPLE ROW UPDATE
• UPDATE WITH SUBQUERY

SINGLE ROW UPDATE:

This update operation will update only one row of a table.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;

We want to update the salary of EMPLOYEE_ID 208 to 2500.

UPDATE EMP_SELECT
SET SALARY = 2500
WHERE EMPLOYEE_ID = 208;

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;

MULTIPLE ROW UPDATE:

This update operation will update multiple rows of a table.

UPDATE EMP_SELECT
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID = 30;

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;

In the above example we have updated all the salaries of DEPARTMENT_ID 30 with 10% increment.

UPDATE WITH SUBQUERY:

We are inserting a new row in EMP_SELECT table.

INSERT INTO EMP_SELECT (EMPLOYEE_ID, 
          FIRST_NAME, 
          LAST_NAME, 
          SALARY, 
          DEPARTMENT_ID
         ) 
VALUES ('212', 
        'Robin', 
        'Smith', 
        '3000', 
        '30'
       );

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;

Now, we will update the JOB_ID and HIRE_DATE of EMPLOYEE_ID 212 with the same of EMPLOYEE_ID 211.

UPDATE EMP_SELECT
SET JOB_ID = (SELECT JOB_ID
              FROM EMP_SELECT
              WHERE EMPLOYEE_ID = 211),
    HIRE_DATE = (SELECT HIRE_DATE
                 FROM EMP_SELECT
                 WHERE EMPLOYEE_ID = 211)
WHERE EMPLOYEE_ID = 212;

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;

RELATED TOPICS:

• INSERT IN ORACLE
• MULTIPLE TABLE INSERT