Using UPDATE Statement in Oracle Database 23ai

The UPDATE statement in Oracle Database 23ai is used to change existing data in a table. Instead of inserting new rows, UPDATE helps you adjust or fix information that is already stored. This tutorial explains how to use UPDATE step by step with practical examples. You will also see how Oracle Database 23ai extends the UPDATE statement to work with modern features like JSON and vector data. By the end, you will be able to update values safely and effectively.

Understanding the UPDATE Statement

The UPDATE statement modifies one or more rows in a table. You choose which columns to change and, optionally, add a condition to control which rows are affected.

The basic structure looks like this:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

COMMIT;

• UPDATE tells Oracle which table you want to change.
• SET lists the columns and the new values.
• WHERE limits the update to specific rows. If you skip WHERE, all rows will be updated.
• COMMIT saves the changes permanently.

For data preparation, refer to the following tutorials to create tables and insert data:

1. How to Create a Table in Oracle 23ai
2. How to Insert Data into a Table in Oracle 23ai

Updating a Single Column

Suppose you want to increase an employee’s salary in the employees table. The following query increases John’s salary to 9000:

UPDATE employees
SET salary = 9000
WHERE first_name = 'John';

COMMIT;

Updating Multiple Columns

You can change several columns at once in the same row. Suppose Alice gets a salary adjustment and a new department:

UPDATE employees
SET salary = 12500,
    department_id = 30
WHERE first_name = 'Alice';

COMMIT;

Updating Multiple Rows

The WHERE clause can match multiple rows. For example, let’s give all employees in department 20 a salary raise:

UPDATE employees
SET salary = salary + 500
WHERE department_id = 20;

COMMIT;

Updating All Rows

If you leave out the WHERE clause, every row in the table will be updated. Use this carefully.

UPDATE employees
SET commission_pct = 0.05;

COMMIT;

Using Subqueries in UPDATE

UPDATE can use a subquery to get new values from another table. Suppose we want Sophia’s department to match David’s department:

UPDATE employees
SET department_id = (
   SELECT department_id
   FROM employees
   WHERE first_name = 'David'
)
WHERE first_name = 'Sophia';

COMMIT;

Using UPDATE with Conditions

You can use operators like <, >, =, AND, OR, and IN to control updates. For example, increase salaries for all employees earning less than 7000:

UPDATE employees
SET salary = salary + 1000
WHERE salary < 7000;

COMMIT;

Using UPDATE with CASE Expressions

CASE lets you apply conditional logic within an UPDATE. This allows different rows to be updated differently in one statement.

UPDATE employees
SET salary = CASE
               WHEN salary < 6000 THEN salary + 1500
               WHEN salary BETWEEN 6000 AND 10000 THEN salary + 1000
               ELSE salary + 500
             END;

COMMIT;

Updating JSON Columns in Oracle 23ai

Oracle 23ai allows you to update JSON fields directly when the column is declared as JSON. For example, in the products table:

UPDATE products
SET product_data = JSON_TRANSFORM(product_data,
                                  SET '$.name' = 'Gaming Laptop')
WHERE product_id = 1;

COMMIT;

Updating Vector Data in Oracle 23ai

Vector columns can also be updated. Suppose you want to adjust the embedding of document 2 in the documents table:

UPDATE documents
SET embedding = VECTOR('[0.25, 0.70, 0.65]')
WHERE doc_id = 2;

COMMIT;

Best Practices for UPDATE

1. Always use a WHERE clause unless you want to change all rows.
2. Test your condition with a SELECT first to see which rows will be affected.
3. Make backups or use transactions (COMMIT and ROLLBACK) for safety.
4. Use CASE when you need to apply different updates to different rows.
5. In Oracle 23ai, explore JSON and vector updates to manage AI-driven data more effectively.

Conclusion

The UPDATE statement in Oracle Database 23ai is a powerful way to modify existing data. From adjusting single values to updating JSON and vector columns, it provides flexibility for both classic business use cases and modern AI-powered applications. With careful use of conditions, subqueries, and CASE expressions, you can ensure updates are accurate and efficient.

Note: All the UPDATE statement examples in this tutorial were tested and verified on Oracle Database 23ai using Oracle SQL Developer version 24.

See also:

1. Installing Oracle Database 23ai on Windows
2. Installing Oracle SQL Developer on Windows