Updating data in 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;
Updating data in Oracle: output

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;
Updating data in Oracle : output

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;
Updating data in Oracle : output

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: