Refresh

This website www.w3schools.blog/update-query-oracle is currently offline. Cloudflare\'s Always Online™ shows a snapshot of this web page from the Internet Archive\'s Wayback Machine. To check for the live version, click Refresh.

UPDATE Query in Oracle

ORACLE UPDATE
To update the existing records in a table, the Oracle UPDATE statement is used.

Syntax: To Update a table in simple steps.

UPDATE table_name
SET column_1 = expr_1,  
    column_2 = expr_2,  
    ...  
    column_n = expr_n  
WHERE conditions;  

Syntax: To Update Table by selecting records from another table.

UPDATE table_name  
SET column_1 = (SELECT expression
               FROM source_table
               WHERE conditions)  
WHERE conditions;   

Parameters:
table_name: It is used to specify the name of the table whose records need to be updated.
column_1, column_2, … column_n: It is used to specify the columns of the table whose values needs to be updated.
expression_1, expression_2, … expression_n: It is used to specify the values to be updated to the respective columns. For example, expression_1 will be inserted into column_1, expression_2 to column_2 and so on.
source_table: It is used to specify the table from which records will be selected and then will be updated to the desired table.
conditions: It is used to specify the conditions to be strictly satisfied in order to have a successful execution of the Update statement.

Example: Updating a single column of a table.
Students table before insertion:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 5
2 Smiley 13
3 Happy 11

Query:

UPDATE students  
SET student_name = 'Jacob'  
WHERE student_id = 3;

Explanation:
The student_name will be updated as ‘Jacob’ where student_id is 3.
Students table after insertion:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 5
2 Smiley 13
3 Jacob 11

Example: Updating multiple columns of a table.
Students table before insertion:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 5
2 Smiley 13
3 Happy 11

Query:

UPDATE students  
SET student_name = 'Jacob', student_age = 15  
WHERE student_id = 3;

Explanation:
The student_name will be updated as ‘Jacob’, and the student_age will be updated as 15 where student_id is 3.

Students table after insertion:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 5
2 Smiley 13
3 Jacob 15

Example: Selecting records from another table.

UPDATE students  
SET student_name = (SELECT name  
                 FROM children 
                 WHERE children.name = students.student_name)  
WHERE age < 15;

Explanation:
The ‘students’ table will be updated by retrieving the data from the ‘children’ table.