This page is based on examples to be easier to follow. The Oracle Update allowing to change or update existing values in Oracle table or materialized view without deleting or inserting new rows. The statement is regulated by ANSI standard and the basic Oracle Update syntax is:
UPDATE <table> SET <column> = <new_value> [ WHERE <the_condition(s)> ];
The first example will use table CUSTOMERS that has two columns FORENAME and SURNAME. We are going to change all “John” names to “Sean” and the SQL statement is following:
UPDATE customers SET forename = 'Sean' WHERE forename = 'John';
As you the SQL output shows we did amend one row’s data. To update more than one column’s data we will just add additional column to the Oracle Update statement as it is done on the next example. On the next example we will change all “John” forenames to “Sean” and their new surname will become “Longs“.
UPDATE customers
SET forename = 'Sean',
surname = 'Longs'
WHERE forename = 'John';
Once again a line has been updated. Similar to the last example you can add as many columns you need to the update statement and change their values depending on the WHERE condition.
The third example will show how to use Oracle Select query in the Oracle Update statement. You can use this statement to change the column values with taken from another existing table. The same condition applies here as on the last example and all John names will be changed to “Sean Longs”
UPDATE customers
SET (forename,surname) = (SELECT ('Sean','Longs') FROM dual)
WHERE forename = 'John';
The output shows the same result as on all last pictures – one row has been updated. Take a look at the update statement we are using the Oracle Dual table to return the values but this has been done here only for the example purpose. You can use in the same way any Oracle table or a view, but it is important to know that column types and amount has to match in both brackets or your update query will raise an error and fails to complete the change.
The second hint about using select queries in the Oracle Update is to use as much as possible ID (unique identification) values in joining. When you do so your Oracle Select wouldn’t slow down your update statement too much.
Once you’ll become a master in writing Oracle updates there are still a few more things to know especially about updating big tables. We are recommending not use Oracle Update on very big Oracle tables or using it with SQL queries that returning huge amount of rows. And that mainly because Oracle Update is one of the slowest process and using it with big data amounts will slow down your entire Oracle database. You can use instead:
- An Oracle Insert with Oracle global temporary tables. Basically you should write an Oracle Select in the way you want to look the updated lines and insert them into Oracle temporary table.
- For next delete the existing lines from the original table that needs to be updated.
- And for last do another Insert from your temporary table to the final table that was supposed to be updated in the first place.
Following this style all the updating process will finish much faster and you will give your code much better performance.
See Also:
Oracle Select Oracle Insert Oracle Delete Home