Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle UPDATE

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?
  • YesNo
Previous Oracle INSERT INTO SELECT
Next Oracle DELETE

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.