How To Write UPDATE Statement In Oracle

How To Write UPDATE Statement In Oracle

October 15, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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';

odba oracle dba database management system database oracle performance tuning oracle update oracle update in oracle sql update in oracle sql update oracle sql oracle database software database oracle

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';

dba oracle dba database management system database oracle performance tuning oracle update oracle update in oracle sql update in oracle sql update oracle sql oracle database software database oracle

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';

dba oracle dba database management system database oracle performance tuning oracle update oracle update in oracle sql update in oracle sql update oracle sql oracle database software database oracle

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*