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.

Please follow and like us:
Content Protection by DMCA.com