Home » Oracle SQL » Oracle UPDATE Statement with Practical Examples

Oracle UPDATE Statement with Practical Examples

Oracle UPDATE statement is used to update existing values in a table.

Syntax

The syntax of Oracle UPDATE statement is as follows:

UPDATE
    table_name
SET
    column1 = value1,
    column2 = value2,
    column3 = value3,
    ...
WHERE
    condition;

Here,

table_name : It’s the name of table within which we want to update values.

SET column1=value1 : Here, we define the column name and new value that will replace existing value. The new value can be a literal value or a subquery which ultimately returns a single value.

WHERE condition : This is the condition based on which the value is updated.

The UPDATE statement can be used to update multiple rows at the same time. The WHERE condition is optional in the syntax.

Oracle UPDATE Examples

Let’s create a new table with some dummy data for these examples:

CREATE TABLE interns (
    intern_id NUMBER PRIMARY KEY,
    intern_name VARCHAR2(255) NOT NULL,
    salary NUMBER NOT NULL,
    location_name VARCHAR2(255) NOT NULL,
    hire_date DATE NOT NULL
);

Let’s insert some dummy data into this table using INSERT statement:

INSERT INTO interns(intern_id, intern_name,salary,location_name,hire_date)
VALUES(1,'Rock',3000,'Texas', DATE '2020-02-14');
INSERT INTO interns(intern_id, intern_name,salary,location_name,hire_date)
VALUES(2,'Obama',2500,'Los Angeles', DATE '2020-02-18');
INSERT INTO interns(intern_id, intern_name,salary,location_name,hire_date)
VALUES(3,'Jane',3500,'New York', DATE '2020-03-20');
INSERT INTO interns(intern_id, intern_name,salary,location_name,hire_date)
VALUES(4,'Shiv',8000,'New York', DATE '2020-04-03');
INSERT INTO interns(intern_id, intern_name,salary,location_name,hire_date)
VALUES(5,'Mickey',7000,'Sydney', DATE '2020-05-10');
INSERT INTO interns(intern_id, intern_name,salary,location_name,hire_date)
VALUES(6,'Britney',3000,'Texas', DATE '2020-07-14');
INSERT INTO interns(intern_id, intern_name,salary,location_name,hire_date)
VALUES(7,'Shakira',7500,'California', DATE '2020-09-22');

Let’s Use SELECT query to have a look at all the data in the table,

SELECT * FROM interns;

Result:

1. Oracle UPDATE – Updating one column in Single Row

Here, we use UPDATE statement to update salary of employee named ‘Obama’ having intern_id =2:

UPDATE
    interns
SET
    salary = 7000
WHERE
    intern_id = 2;

Let’s write one query to check if the value has been updated,

SELECT
    *
FROM
    interns
WHERE
    intern_id = 2;

Result:

2. UPDATE – Updating Multiple Columns in a Single Row

UPDATE
    interns
SET
    salary = 5000,
    location_name = 'San Francisco',
    hire_date = DATE '2020-12-12'
WHERE
    intern_id = 1;

Let’ check the table:

SELECT * FROM interns;

Result:

3. ORACLE UPDATE – Updating Multiple Rows in One Instance

UPDATE
    salary
SET
    salary = salary * 1.20;

Let’s check the table:

As we can see, the salary of interns has been increased by over 20% in all the rows.

4. UPDATE table using a Subquery

We can use data from other tables using a Subquery and inserting it into our table.

UPDATE interns SET
intern_ID = ( SELECT intern_id from juniors WHERE intern_id = 10),
salary = (SELECT salary from juniors WHERE intern_id = 10)
WHERE intern_id = 1 ;

Result: