A Guide to the Oracle UPDATE StatementHave you ever needed to update data that was already in a table in Oracle? Learn how to do this with the Oracle UPDATE Statement.

What Is the Oracle UPDATE Statement?

The Oracle SQL UPDATE statement allows you to change data that is already in a table in SQL.

The INSERT statement lets you add data to the table, and the DELETE statement lets you remove data from a table.

But the UPDATE statement changes the data in the table, without deleting it. It saves you having to delete and re-insert the data.

 

What is the Oracle UPDATE Statement Syntax?

The syntax for the Oracle UPDATE statement is:

UPDATE tablename

SET column1 = value1,

column2 = value2,

column_n = value_n

[WHERE condition];

The UPDATE statement allows you to update data from another table, using a SELECT statement. The syntax for this is:

UPDATE tablename

SET column = (SELECT query)

[WHERE condition];

 

UPDATE Statement Parameters

The parameters for the Oracle SQL UPDATE statement are:

  • tablename: The name of the table you want to update.
  • column1/2/n: The column whose value you want to update.
  • value1/2/n: The value or expression to use for the new value.
  • condition: The condition in the WHERE clause to specify which rows to update. This is optional. If it is not provided, then all records in the table are updated.
  • query: The SELECT query that returns the value you want to set the column to.

Let’s have a look at some examples of the UPDATE SQL statement.

 

Oracle UPDATE Statement Examples

Example 1 – Update a Single Column

We’ll update a single column and single row in this example.

UPDATE student
SET fees_paid = 100
WHERE student_id = 2;

This has updated the student record with a student_id of 2, and set the fees_paid value to 100.

Example 2 – Update Multiple Columns

You can update multiple columns in the same table in a single UPDATE statement.

UPDATE student
SET fees_paid = 500,
fees_required = 1000
WHERE student_id = 4;

This has updated both the fees_paid and fees_required fields for the student record with a student_id of 4. Note that there is a comma separating the fees_paid = 500 and the fees_required.

Example 3 – Update Using a Subquery

As mentioned in the syntax example earlier, you can use a subquery inside the UPDATE statement to set a value based on a query.

UPDATE student
SET fees_required = (
  SELECT MAX(fees_required)
  FROM student)
WHERE student_id = 5;

This example will update the fees_required value of the record with a student_id of 5. The value it will set it to is the MAX of the fees_required value for all records in the student table.

Notice that the WHERE clause is outside the brackets, meaning the condition applies to the UPDATE statement and not the SELECT statement.

Example 4 – Subquery in the WHERE Clause

You can also run an UPDATE statement and use a subquery in the WHERE clause.

UPDATE student
SET fees_paid = 400
WHERE fees_required = (
  SELECT MAX(fees_required)
  FROM student
);

This query will find the MAX of the fees_required field, find all rows that match that value, and update their fees_paid to 400.

Example 5 – Update Using an EXISTS in the WHERE Clause

You can use the EXISTS keyword in the WHERE clause along with a subquery. This is often a more efficient way of filtering (but not always).

UPDATE student
SET fees_paid = 350
WHERE EXISTS (
  SELECT student_id
  FROM address
  WHERE address.address_id = student.address_id
);

This will update the fees_paid value only where a matching value in the address table is found. This is just an example, but you can use the EXISTS function in a more meaningful way.

Example 6 – No WHERE Clause Specified

Because the WHERE clause is optional, you can run an Oracle UPDATE statement without it. It will update every row in the table.

UPDATE student
SET fees_required = 100;

This will set the fees_required value to 100 for every record in the table. This is most likely not what you want to do – so be careful!

Example 7 – Update Using Inline View

Another way of updating only the values you want is by using an inline view.

Instead of specifying just the table, you can use a subquery, which is an inline view. This is also called an “update select”.

An example of this would be:

UPDATE (
  SELECT student_id,
  first_name,
  last_name,
  fees_paid,
  fees_required
  FROM student
  WHERE student_id = 2) std
SET std.fees_paid = 450;

This is another way of updating a single record using the Oracle SQL Update command.

Example 8 – Updating Two Columns Using a Subquery

You can also use a subquery to update two columns at once. This can be easier to read and write, especially for large queries.

UPDATE student s
SET (s.fees_paid, s.fees_required) = (
  SELECT MAX(fees_paid), MAX(fees_required)
  FROM student t
)
WHERE student_id = 6;

This will update both columns using the one SELECT subquery. It saves you having to write the query twice (once for each column) and therefore saves Oracle from running it twice.

 

Can You Update Multiple Tables in a Single UPDATE Statement?

No, you can’t. You can only update a single table in an UPDATE statement.

You’ll need to write separate statements to update a different table.

 

Why Is My Oracle UPDATE Statement Not Working?

Your UPDATE statement might not be working for a variety of reasons. Some steps I usually take when I have issues with my UPDATE statement are:

Convert the UPDATE statement to a SELECT statement

Convert the statement to a SELECT and see which rows are returned. This will give you a preview of which rows will be changed.

If no rows are being returned, it means your UPDATE statement won’t update anything, and you won’t get the results you’re looking for.

Check the Data Types

You might be trying to set a value in a column where the data type doesn’t match.

To find the data type of a column, you can run this command (replace YOUR_TABLE_NAME with the actual name of your table, in upper case):

SELECT column_name, data_type, data_length, data_precision
FROM SYS.USER_TAB_COLUMNS
WHERE table_name = 'YOUR_TABLE_NAME';

Then, check the data type of the value your setting the column to. You can run a SELECT statement on this value if it’s from a table, or look at it and see if it matches.

You could run a SELECT FROM DUAL on the value, and use the DUMP function to see the data type as well.

Review the Logic

Perhaps you need to review the logic of your update statement if it’s not giving the results you are expecting.

Are you missing a join in a subquery? Are you selecting from the right field? Is your WHERE condition correct?

Double-check the query to make sure it’s correct.

Ask For a Code Review

If you still can’t get the UPDATE statement working, then perhaps you can ask a team member to review it.

Sometimes they can spot things you don’t, because you’ve been looking at it for so long. That’s happened to me many times.

They might also know the system a bit better or might have had the issue before. In any case, asking a coworker for a review might help you correct your query.

 

So, that brings us to the end of the Oracle UPDATE Statement guide. If you have any questions on the UPDATE statement, leave a comment below

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Improve Your Oracle SQL With My 10-Day Email Course

x