How To Write UPDATE Statement In Oracle

This tutorial page is based on examples to make it easier to follow. Statement Oracle UPDATE allows to amend the data in existing rows. The Oracle Update statement is regulated by ANSI standard and the basic syntax of is:

UPDATE <table>
   SET <column> = <your_value>;

The first Oracle Update example will use table CUSTOMERS where columns are FORENAME and SURNAME. The statement will update all “John” names to “Sean“.

UPDATE customers
   SET forename = 'Sean'
 WHERE forename = 'John';

To update more than one column use the next Oracle Update statement. It does amend all existing rows who’s FORENAME is “John” to “Sean” and the SURNAME column will be set to “Longs“:

UPDATE customers
   SET forename = 'Sean',
       surname = 'Longs'
 WHERE forename = 'John';

The third example is demonstrating how to use Oracle Select statement to update table CUSTOMERS columns FORENAME and SURNAME. The update is using Oracle Dual table that is present in all Oracle database versions. We do update all rows who’s FORENAME is “John” and the new values will be FORENAME to “Sean” and SURNAME to “Longs“.

UPDATE customers
   SET (forename,surname) = (SELECT ('Sean','Longs') FROM dual)
 WHERE forename = 'John';

Our Oracle DBA recommends to use an ID column to join in the Oracle Select query and that way the Oracle Update statement will work more faster.

Also we do recommend not to use Oracle Update for big Oracle tables or on a big amount of rows because the Update statement is quite slow procedure and it will slow down all your Oracle database processes. We do recommend to use Oracle Insert and Oracle global temporary tables instead of Oracle Update. Basically you’ll write Oracle Select in the way you want to look like updated values and inserting the lines into Oracle temporary table. Deleting the lines that needed to be updated from the original source table and making another Oracle Insert from your temporary Oracle table to the original source table. The way the all process will finish much faster and it will be your Oracle performance tuning.

See Also:
Oracle Select Oracle Create Table Oracle Insert Home