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.

Alex Martin