Oracle UPDATE

Summary: in this tutorial, you are going to learn how to use the Oracle UPDATE statement to change existing values in a table.

Introduction to the Oracle UPDATE statement

To changes existing values in a table, you use the following Oracle UPDATE statement:

UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3, ... WHERE condition;
Code language: SQL (Structured Query Language) (sql)

Let’s examine the UPDATE statement in detail.

  • First, you specify the name of the table which you want to update.
  • Second, you specify the name of the column whose values are to be updated and the new value. If you update more than two columns, you separate each expression column = value by a comma. The value1, value2, or value3 can be literals or a subquery that returns a single value. Note that the UPDATE statement allows you to update as many columns as you want.
  • Third, the WHERE clause determines which rows of the table should be updated. The WHERE clause is optional. If you omit it, the UPDATE statement will update all rows of the table.

Oracle UPDATE examples

Let’s create a new table with some sample data for the demonstration.

First, the following CREATE TABLE statement creates a new table named parts:

CREATE TABLE parts ( part_id NUMBER GENERATED BY DEFAULT AS IDENTITY, part_name VARCHAR(50) NOT NULL, lead_time NUMBER(2,0) NOT NULL, cost NUMBER(9,2) NOT NULL, status NUMBER(1,0) NOT NULL, PRIMARY KEY (part_id) );
Code language: SQL (Structured Query Language) (sql)

Second, the following INSERT statements add sample data to the parts table:

INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('sed dictum',5,134,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('tristique neque',3,62,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('dolor quam,',16,82,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('nec, diam.',41,10,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('vitae erat',22,116,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('parturient montes,',32,169,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('metus. In',45,88,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('at, velit.',31,182,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('nonummy ultricies',7,146,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('a, dui.',38,116,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('arcu et',37,72,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('sapien. Cras',40,197,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('et malesuada',24,46,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('mauris id',4,153,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('eleifend egestas.',2,146,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('cursus. Nunc',9,194,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('vivamus sit',37,93,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('ac orci.',35,134,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('arcu. Aliquam',36,154,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('at auctor',32,56,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('purus, accumsan',33,12,1);
Code language: SQL (Structured Query Language) (sql)

Third, we have a parts table with some sample data for practice:

SELECT * FROM parts ORDER BY part_name;
Code language: SQL (Structured Query Language) (sql)
Oracle UPDATE - sample table

A) Oracle UPDATE – update one column of a single row

The following UPDATE statement changes the cost of the part with id 1:

UPDATE parts SET cost = 130 WHERE part_id = 1;
Code language: SQL (Structured Query Language) (sql)

To verify the update, you use the following query:

SELECT * FROM parts WHERE part_id = 1;
Code language: SQL (Structured Query Language) (sql)
Oracle UPDATE - update single column of a row

B) Oracle UPDATE – update multiple columns of a single row

The following statement updates the lead time, cost, and status of the part whose id is 5.

UPDATE parts SET lead_time = 30, cost = 120, status = 1 WHERE part_id = 5;
Code language: SQL (Structured Query Language) (sql)
Oracle UPDATE - update multiple column of a row

C) Oracle UPDATE – update multiple rows example

The following statement increases the costs of all parts in the parts table 5%:

UPDATE parts SET cost = cost * 1.05;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

Oracle UPDATE - update all rows

In this tutorial, you have learned how to use the Oracle UPDATE statement to change existing values in a table.

Was this tutorial helpful?