Syntax and Basic Usage
The fundamental syntax for an Oracle SQL UPDATE statement is:
UPDATE table_name
SET column1 = value1,
column2 = value2,
...
WHERE condition;table_name specifies the table requiring the update. Columns (column1, column2, …) and their corresponding new values replace old data in the SET clause. The optional WHERE clause restricts the update to specific rows.
For example, to increase all salaries in the employees table by 5%:
UPDATE employees
SET salary = salary * 1.05;The SET clause can handle multiple columns:
UPDATE employees
SET job_id = 'SA_MAN',
department_id = 120
WHERE employee_id = 103;Subqueries can be used to base updates on values from connected tables:
UPDATE employees
SET department_id = (
SELECT department_id
FROM departments
WHERE location_id = 2100)
WHERE employee_id = 103;Oracle Database 23c supports Direct Joins in UPDATE and DELETE operations. For example, to update all Marketing department salaries in Toronto by 10%:
UPDATE employees e
SET e.salary = e.salary * 1.10
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE d.department_name = 'Marketing' AND l.city = 'Toronto';To delete a specific job history entry for an employee:
DELETE FROM job_history j
USING employees e
WHERE j.employee_id = e.employee_id
AND e.first_name = 'Neena'
AND j.job_id = 'AC_ACCOUNT';Updating partitioned tables requires specifying the partition:
UPDATE sales PARTITION (sales_q1_1999)
SET promo_id = 494
WHERE amount_sold > 1000;The RETURNING clause can be used to handle errors or capture return values:
UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;
Using the REPLACE Function
The Oracle SQL REPLACE function is useful for dynamically changing parts of strings within a database. It’s particularly effective when used with the UPDATE statement to efficiently alter substrings within columns.
For example, to update email domains across a users table from old_domain.com to new_domain.com:
UPDATE users
SET email = REPLACE(email, 'old_domain.com', 'new_domain.com'); To update email domains only for users in a specific city:
UPDATE users
SET email = REPLACE(email, 'old_domain.com', 'new_domain.com')
WHERE city = 'New York';The REPLACE function can be used for any substring modifications. For instance, updating product codes when the format changes:
UPDATE products
SET product_code = REPLACE(product_code, 'OLD', 'NEW');Combining REPLACE with the WHERE clause refines updates further. For example, changing phone number prefixes for specific regions:
UPDATE contacts
SET phone_number = REPLACE(phone_number, '123', '456')
WHERE region = 'West Coast'; For complex expressions, REPLACE can be used within correlated updates. For example, updating URLs to maintain SEO practices:
UPDATE pages p
SET p.url = REPLACE(p.url, 'http', 'https')
WHERE EXISTS (
SELECT 1
FROM security_audits sa
WHERE sa.page_id = p.id
AND sa.recommendation = 'Upgrade to HTTPS'
);According to a study by HubSpot, nearly 80% of users will leave a website if it does not display properly on their device.1 Therefore, using REPLACE to maintain consistent URLs and domain names across devices can significantly impact user engagement and retention.
Updating Multiple Columns
To update multiple columns simultaneously in an UPDATE statement, provide a comma-separated list of column/value pairs in the SET clause. This ensures each column you wish to update is clearly defined alongside its new value.
For example, to set both job titles and salaries for employees in department 120:
UPDATE employees
SET job_id = 'SA_MAN',
salary = salary * 1.05
WHERE department_id = 120;Multiple updates can be conditional. To update both order status and shipping date for orders ready to be shipped:
UPDATE orders
SET status = 'Shipped',
shipping_date = SYSDATE
WHERE status = 'Ready' AND destination = 'New York';Subqueries can streamline updates with multiple columns. To update columns based on another table, like adjusting employee records based on a linked departments table:
UPDATE employees e
SET e.department_name = (
SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id),
e.manager_name = (
SELECT m.manager_name FROM managers m WHERE m.department_id = e.department_id)
WHERE e.location_id = 1500;Expressions can be leveraged directly within the SET clause for more complex logic. For example, applying discounts while setting a flag for audit purposes in a sales table:
UPDATE sales
SET discount = CASE
WHEN sale_amount > 1000 THEN 0.10
ELSE 0.05
END,
audit_flag = 'Updated'
WHERE sale_date = TRUNC(SYSDATE) - 1;Updating multiple columns in a single statement offers several advantages:
- Improves code readability and maintainability
- Reduces network traffic and round trips to the database
- Allows for complex, conditional logic within the
SETclause
Updating Using Data from Another Table
To update a table using data from another table, subqueries and joins can be used to simplify operations and improve performance.
For example, to update estimated_delivery dates in an orders table based on data from a shipping table:
UPDATE orders o
SET o.estimated_delivery = (
SELECT s.estimated_delivery
FROM shipping s
WHERE o.order_id = s.order_id
)
WHERE o.status = 'Pending';For more complex scenarios or larger datasets, using a direct join in an UPDATE statement can be more efficient. Oracle 23c enhances this with Direct Joins. To update stock levels in an inventory table based on sales data:
UPDATE inventory i
SET i.stock = i.stock - s.quantity_sold
FROM inventory i
JOIN sales s ON i.product_id = s.product_id
WHERE s.sale_date = TRUNC(SYSDATE) - 1;Advanced techniques with subqueries can handle even more complex requirements. To update employees’ performance ratings based on their latest evaluations:
UPDATE employees e
SET e.performance_rating = (
SELECT pr.rating
FROM performance_reviews pr
WHERE e.employee_id = pr.employee_id
AND pr.review_date = (SELECT MAX(pr2.review_date)
FROM performance_reviews pr2
WHERE pr2.employee_id = e.employee_id)
)
WHERE EXISTS (
SELECT 1
FROM performance_reviews pr
WHERE e.employee_id = pr.employee_id
); To adjust multiple columns based on another table, the SET clause can be extended with complex expressions. For example, updating both department and salary fields in an employees table based on a recent reorganization:
UPDATE employees e
SET e.department_id = (
SELECT d.new_department_id
FROM departments d
WHERE e.department_id = d.old_department_id),
e.salary = (
SELECT d.adjusted_salary
FROM departments d
WHERE e.department_id = d.old_department_id)
WHERE e.status = 'Active';Precise condition handling is important when updating using data from another table to avoid unintended data modifications. Subqueries and joins should be used judiciously to ensure efficient and accurate updates. A study by Oracle found that proper indexing and join techniques can improve query performance by up to 90%.2
Advanced Update Techniques: Direct Joins in 23c
Oracle Database 23c introduces Direct Joins in UPDATE statements, enhancing performance and simplifying SQL operations for complex, multi-table updates. This feature streamlines code while ensuring efficiency during data modification tasks.
Direct Joins in the FROM clause of an UPDATE statement harmonize the update process by eliminating subqueries and reducing code. It enhances readability and maintainability, making it easier to manage large-scale updates across interconnected tables.
For example, updating salaries for employees in a particular department and city:
UPDATE employees e
SET e.salary = e.salary * 1.10
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE d.department_name = 'Marketing' AND l.city = 'Toronto';
The UPDATE statement benefits from the clear structure and efficient execution provided by Direct Joins. Employees in Toronto’s Marketing department receive a 10% salary increase, with the joins simplifying and accelerating the query.
Direct Joins also streamline operations like removing job history records for a specific employee:
DELETE FROM job_history j
USING employees e
WHERE j.employee_id = e.employee_id
AND e.first_name = 'Neena'
AND j.job_id = 'AC_ACCOUNT';
This syntax simplifies the deletion process by integrating the join condition into the DELETE operation, facilitating clearer and faster maintenance of job history data.
Direct Joins optimize performance by allowing the database to better understand relationships and dependencies between tables during updates. This is beneficial in scenarios involving partitioned tables, where efficient data handling is crucial. Updating promotion IDs in a quarterly sales partition:
UPDATE sales PARTITION (sales_q1_1999)
SET promo_id = 494
WHERE amount_sold > 1000;
The update targets a specific partition, reducing the operation scope and boosting performance. The Direct Join feature ensures efficient application of business rules at partition levels.
Integrating error handling and capturing return values becomes more seamless with Direct Joins. Updating employee details and capturing the change for audit purposes:
UPDATE employees e
SET e.salary = e.salary + 1000,
e.department_id = 140
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.last_name = 'Jones'
RETURNING e.salary * 0.25, e.last_name, e.department_id
INTO :bnd1, :bnd2, :bnd3;
This ensures real-time capture and handling of updated values, providing a robust solution for auditing and logging update operations.
The Oracle 23c Direct Join feature empowers you to craft efficient, maintainable, and scalable SQL updates. It facilitates clearer, more concise code, reduces the overhead of subqueries, and enhances performance, enabling you to manage complex updates with greater ease and precision. Utilizing this advanced feature will streamline your database operations, delivering improvements in both performance and readability across your SQL scripts.
Let Writio’s AI create your website content! This page was written by Writio
SQL WHERE String Contains: How to Filter Data with Substrings
