Overview

Oracle SQL UPDATE statement can be used to update existing data in an Oracle database table. UPDATE is a DML (Data Manipulation Language) statement.

Syntax

Simple UPDATE syntax:

UPDATE <tableName>

SET columnName = <newValue>

WHERE <condition to identify row>

Example

In this example, we will update a single row of the dept table. Assume that there was a faulty insert for the deptno = 1000 for the location. The correct location of the department is ‘HOUSTON’. 

We can correct the data with an UPDATE statement. The following statement will update the loc for the deptno=1000

UPDATE dept
SET loc='HOUSTON'
WHERE deptno = 1000;

We identified the row to be updated with the WHERE clause and the SET specified the new value for the column of the identified row.

Notice that there was an accidental space before the city name in the Update statement. Issue an other update statement to correct the leading space in the city name. {Left as an exercise for the reader.}

Successful update would result in the following message:

1 row updated.

If the WHERE condition doesn’t match any rows in the table no rows would be updated. The following message would be displayed.

0 rows updated.

To reflect the changes into the database, issue the COMMIT command.

—

Oracle Database Tutorials on this website:

https://www.testingdocs.com/oracle-database-tutorials-for-beginners/

More information about Oracle Database:

https://www.oracle.com/database/