“I want to register data using search results from another table.”
“I want to update rows matching specific conditions based on values from another table.”
Facing these requirements while operating Oracle Database is a daily occurrence. You do not need to manually input values one by one or write loop processing in a program. By utilizing a Subquery, you can complete complex data operations within a single SQL statement.
This article explains implementation patterns of subqueries in DML (INSERT / UPDATE / DELETE) targeting Oracle Database 19c, along with common pitfalls encountered in practice.
Conclusion: Key Points for Using Subqueries
For those short on time, here is a summary of the key points when using subqueries in DML.
- INSERT: By writing a
SELECTstatement instead of aVALUESclause, you can register a large amount of data at once (INSERT SELECT). - UPDATE: Use it in the
SETclause to set values from another table. However, be careful that “an error occurs if the return value is multiple rows” and “it updates to NULL if the condition does not match.” - DELETE: Use it in the
WHEREclause to narrow down deletion targets based on conditions from another table. - Recommendation: For complex conditional branching or updating large amounts of data, consider using the MERGE statement.
1. Basic Knowledge: What are DML and Subqueries?
DML (Data Manipulation Language)
These are languages used to manipulate data within a database. The three main commands are:
- INSERT: Creation of data
- UPDATE: Update of data
- DELETE: Deletion of data
Subquery
This refers to another SELECT statement embedded within an SQL statement.
The outer SQL (Main query) receives the result of the inner SQL (Subquery) and executes the process. This enables dynamic processing such as “manipulating Table B using the results of Table A.”
2. Preparing Verification Data
To execute all SQL in this article, create the following tables and data.
Note: Operation has been confirmed on an actual environment (Oracle Database 19c or later).
Prerequisites:
Connect with a user who has appropriate privileges (such as CREATE TABLE, UNLIMITED TABLESPACE).
-- 1. Create Department table (departments)
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50) NOT NULL
);
-- 2. Create Employee table (employees)
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(50) NOT NULL,
salary NUMBER(10, 2),
department_id NUMBER,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- 3. Register data
INSERT INTO departments VALUES (10, 'Sales');
INSERT INTO departments VALUES (20, 'Engineering');
INSERT INTO departments VALUES (30, 'HR');
INSERT INTO employees VALUES (1, 'Alice', 5000, 10);
INSERT INTO employees VALUES (2, 'Bob', 6000, 20);
INSERT INTO employees VALUES (3, 'Charlie', 5500, 10);
INSERT INTO employees VALUES (4, 'David', 7000, 20);
INSERT INTO employees VALUES (5, 'Eve', 4500, 30);
-- 4. Commit changes
COMMIT;
SQL> SELECT * FROM departments;
DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
10 Sales
20 Engineering
30 HR
SQL> SELECT * FROM employees;
EMPLOYEE_ID EMPLOYEE_NAME SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
1 Alice 5000 10
2 Bob 6000 20
3 Charlie 5500 10
4 David 7000 20
5 Eve 4500 30
3. Practical Practice: Utilizing Subqueries in DML
From here, I will explain usage methods for each case: INSERT, UPDATE, and DELETE.
3.1. Subquery in INSERT Statement (INSERT SELECT)
This inserts search results from another table directly into a table. This is the most commonly used technique for data migration or creating summary tables.
Here, we extract only employees in the “Sales department (ID=10) with a Salary of 5000 or more” and copy them to a separate table high_salary_employees.
Procedure:
Create the receiving table
CREATE TABLE high_salary_employees (
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(50),
salary NUMBER(10, 2)
);
Insert data using a subquery
INSERT INTO high_salary_employees (employee_id, employee_name, salary)
SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = 10
AND salary >= 5000;
-- Check results
SELECT * FROM high_salary_employees;
SQL> INSERT INTO high_salary_employees (employee_id, employee_name, salary)
2 SELECT employee_id, employee_name, salary
3 FROM employees
4 WHERE department_id = 10
5 AND salary >= 5000;
2 rows created.
SQL> SELECT * FROM high_salary_employees;
EMPLOYEE_ID EMPLOYEE_NAME SALARY
----------- -------------------- ----------
1 Alice 5000
3 Charlie 5500
Points:
- The
INSERT INTO ... VALUES ...syntax is not required. - The column order and data types must match.
3.2. Subquery in UPDATE Statement
Use this when you want to “update with values from another table based on certain conditions.”
Here, we will perform a process to “rewrite the salaries of employees in the Engineering department to the highest salary amount among all employees.”
Execution SQL:
UPDATE employees
SET salary = (
-- Subquery 1: Get the maximum salary of all employees
SELECT MAX(salary) FROM employees
)
WHERE department_id = (
-- Subquery 2: Get the ID of the Engineering department
SELECT department_id
FROM departments
WHERE department_name = 'Engineering'
);
-- Check results
SELECT * FROM employees WHERE department_id = 20;
SQL> UPDATE employees
2 SET salary = (
3 SELECT MAX(salary) FROM employees
4 )
5 WHERE department_id = (
6 SELECT department_id
7 FROM departments
8 WHERE department_name = 'Engineering'
9 );
2 rows updated.
SQL> SELECT * FROM employees WHERE department_id = 20;
EMPLOYEE_ID EMPLOYEE_NAME SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
2 Bob 7000 20
4 David 7000 20
Explanation:
- The subquery in the
SETclause must return a single value (scalar value). - The subquery in the
WHEREclause narrows down the update targets.
3.3. Subquery in DELETE Statement
Use this when you want to “delete rows related to data existing under specific conditions in a master table.”
Here, we will “delete employees belonging to the HR department.”
Execution SQL:
DELETE FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'HR'
);
-- Check results (Success if 0 rows)
SELECT * FROM employees WHERE department_id = 30;
SQL> DELETE FROM employees
2 WHERE department_id = (
3 SELECT department_id
4 FROM departments
5 WHERE department_name = 'HR'
6 );
1 row deleted.
SQL> SELECT * FROM employees WHERE department_id = 30;
no rows selected
Points:
- If multiple “HR” departments exist, or if the subquery might return multiple IDs, it is safer to use the
INoperator instead of=.- Example:
WHERE department_id IN (SELECT ...)
- Example:
4. Troubleshooting (Common Errors)
Here are errors that frequently occur with DML using subqueries and how to deal with them.
| Error Code | Error Message | Cause and Solution |
| ORA-01427 | single-row subquery returns more than one row | The subquery in SET col = (SELECT ...) is returning multiple results. Make the WHERE clause stricter or use MAX() to narrow it down to one record. |
| ORA-00913 | too many values | The number of columns in the INSERT clause and the SELECT clause do not match. Check the column definitions. |
| ORA-02291 | integrity constraint (…) violated – parent key not found | You attempted to INSERT/UPDATE a value into a column with a foreign key constraint that does not exist in the parent table. |
5. Operational and Security Considerations
When using these SQL statements in practice, please consider the following risks.
1. Unintended NULL Update (UPDATE Pitfall)
When using a correlated subquery (a subquery that joins with the outer table) in an UPDATE statement, if the subquery result is 0 rows, the target column is updated to NULL.
To prevent this, use EXISTS in the WHERE clause to narrow down update targets, or implement logic to maintain the values.
2. Impact on Performance
When using IN (SELECT ...) in the WHERE clause, processing may become heavy depending on the data volume. In such cases, consider rewriting it to EXISTS or changing it to a MERGE statement using a join (JOIN).
3. Transaction Control
After executing DML, always perform a COMMIT (confirm) or ROLLBACK (cancel). Depending on the tool, auto-commit may be disabled, which can cause lock waits from other sessions.
6. FAQ: Frequently Asked Questions
Q1. If the subquery result is “0 rows”, does it cause an error?
A. It depends on the case.
- INSERT SELECT: 0 rows are inserted, and no error occurs.
- UPDATE SET = (…): If the subquery returns 0 rows, that column is updated to NULL (no error occurs). Be careful if this is unintended behavior.
Q2. Can I update multiple columns at once?
A. Yes, it is possible. You write it as follows:
UPDATE employees
SET (salary, department_id) = (
SELECT 6000, 30 FROM dual
)
WHERE employee_id = 1;
Q3. I accidentally deleted data with DELETE. Can I restore it?
A. If it is before COMMIT, you can restore it with ROLLBACK;.
If it is after COMMIT, you may be able to reference and restore data from a past point in time using Oracle’s Flashback Query feature (AS OF TIMESTAMP) (depending on settings).
7. Summary
By using subqueries in Oracle SQL DML, you can reduce manual work and perform efficient operations while maintaining data integrity.
- INSERT SELECT is a basic technique for data migration.
- In UPDATE / DELETE, be careful of the ORA-01427 (multiple rows returned) error.
- If the target of the process is ambiguous, always check the execution result with a
SELECTstatement alone before incorporating it into DML.
First, try out the SQL introduced here in your development environment. Once you get used to it, I recommend learning the MERGE statement, which allows for more advanced data manipulation.
コメント