Oracle | UPDATE Statement Tutorial – Real SQL Examples & Subquery Guide

The UPDATE statement is one of the most frequently used DML commands in Oracle for modifying data.


However, it's also surprisingly easy to make mistakes—like running an update without a WHERE clause, or forgetting join conditions altogether.


In this post, we’ll go over the essential syntax of UPDATE, cover real-world use cases including subqueries and SELECT-based updates, and provide key tips to avoid costly mistakes in production environments.




📌 1. Basic UPDATE – Updating a Single Row with Conditions

UPDATE EMP  
SET DEPT = 'Planning'  
WHERE EMP_ID = 1001;


The SET clause assigns new values to columns.
Always include a WHERE clause to avoid unintentionally updating all rows.




📌 2. Updating Multiple Columns – Use Commas to Separate Fields

UPDATE EMP  
SET EMP_NAME = 'Jinwoo Kim', DEPT = 'Development'  
WHERE EMP_ID = 1002;


You can update multiple columns at once using commas.
Performing updates in a single query ensures transactional consistency and is easier to maintain.




📌 3. UPDATE with Subquery – Pulling Data from Another Table

UPDATE EMP
SET DEPT = (
                            SELECT DEPT_NAME
                            FROM DEPT
                            WHERE DEPT.DEPT_ID = EMP.DEPT_ID
)
WHERE EXISTS (
                            SELECT 1
                            FROM DEPT
                            WHERE DEPT.DEPT_ID = EMP.DEPT_ID
);


Subqueries allow you to fetch and apply values from other tables.


The EXISTS clause ensures the subquery only runs when matching rows exist, reducing unnecessary updates.


Great for keeping department names, status values, or other reference data up-to-date in real time.




📌 4. UPDATE with JOIN – Use MERGE or EXISTS Instead

Oracle does not officially support JOIN syntax in standard UPDATE statements.
If you need complex join logic, consider using the MERGE statement or a combination of IN or EXISTS clauses.


UPDATE EMP E  
SET DEPT = (
                            SELECT D.DEPT_NAME
                            FROM DEPT D
                            WHERE D.DEPT_ID = E.DEPT_ID
)  
WHERE E.DEPT_ID IN (
                            SELECT DEPT_ID FROM DEPT
);




📌 5. Checklist to Avoid Common Mistakes

- Missing WHERE clause → all rows will be updated
- Subquery returns multiple rows → ORA-01427: single-row subquery returns more than one row
- Missing JOIN condition → incorrect or duplicated values across rows
- Use transactions : wrap your update in BEGIN TRANSACTION or set a SAVEPOINT to allow safe rollbacks