Theory
How do you change a thousand records with one unknown fact?
Imagine the TicketDesk system experiences an emergency. An agent named Amit has suddenly gone on leave, and you are ordered to immediately escalate all of his open tickets to Critical priority. If you know Amit's agent ID is 4, it is a simple query. But what if you only have his name, and his ID could be any random digit in a massive database? You cannot manually look up the ID first and type it into a second query during an exam or a production crash. You need a way to make one query feed the answer directly into another.
Theory
The Courier with an Inner Envelope
Think of an executive assistant who receives a sealed instruction: 'Deliver this bonus check to the employee who won the top performance award this month.' The assistant does not know who won the award yet. First, they open an inner envelope containing the winner's name, Vijay. Once they read that name, they complete the outer instruction by writing Vijay's name on the check. In SQL, a data modification query can contain a hidden inner query that finds the unknown data value first.
Theory
Defining Subqueries in DML Operations
In Oracle SQL, a Subquery (or nested query) is a SELECT statement embedded inside another SQL statement. When used within Data Manipulation Language (DML) statements like INSERT, UPDATE, or DELETE, the inner subquery runs exactly once to fetch a data value or a list of values. The outer DML statement then uses that result immediately to pinpoint exactly which rows to insert, modify, or remove from the database schema.
At a glance
How subqueries empower different data modification commands in Oracle SQL
| DML Action | Role of Subquery | Practical TicketDesk Example |
|---|---|---|
| INSERT | Provides the rows or values to be added | Copying old resolved tickets into a separate archive table |
| UPDATE | Finds the new value or filters the target rows | Setting ticket status to High for all tickets handled by a specific team |
| DELETE | Identifies the criteria for row removal | Purging all tickets associated with deactivated agent accounts |
Practical
Executing Subqueries within UPDATE and DELETE Statements
-- Step 1: Escalate tickets for an agent when you only know their name
UPDATE tickets
SET priority = 'Critical'
WHERE agent_id = (SELECT id FROM agents WHERE name = 'Amit');
-- Step 2: Delete tickets associated with agents whose names start with Test
DELETE FROM tickets
WHERE agent_id IN (SELECT id FROM agents WHERE name LIKE 'Test%');This example runs in Gri-Learn on the web, where you can edit it and see the output.
Quiz
What will happen if the subquery inside an UPDATE statement 'WHERE agent_id = (SELECT id FROM agents WHERE name = Liam)' returns 3 different IDs?
- Oracle will successfully update all tickets matching any of the 3 IDs
- Oracle will throw a runtime error because a single value operator (=) expects only 1 row
- Oracle will ignore the update entirely and skip to the next command without an error
- Oracle will automatically change the equality operator to an IN operator
Show the answer
Oracle will throw a runtime error because a single value operator (=) expects only 1 row
The equality operator (=) is a single row operator. If the subquery returns multiple rows (like 3 different IDs), Oracle cannot decide which one to use and will throw an error: single-row subquery returns more than one row. To handle multiple rows, you must use the IN operator instead of (=).
Think first
Mental Challenge: Subquery with INSERT
Imagine you have an empty table called history_logs(ticket_id, title). Mentally construct an INSERT statement that uses a subquery to copy the id and title of all tickets with status 'Closed' into this table. Try it before you reveal.
Show the answer
The query is: INSERT INTO history_logs (ticket_id, title) SELECT id, title FROM tickets WHERE status = 'Closed'; Note that when inserting rows directly from a subquery, you do not use the VALUES keyword!
Watch out
The VALUES Keyword Mistake
When using a subquery with an INSERT statement to copy multiple rows, a major mistake that costs exam marks is writing INSERT INTO table VALUES (SELECT ...);. In Oracle SQL, combining VALUES with a multi-row subquery causes a syntax error. Drop the VALUES keyword completely and let the INSERT statement directly follow the data-producing SELECT query structure.
Theory
Real World Operations and Sem 3 Threads
Using subqueries with DML commands is a fundamental skill for database administrators handling batch operations. Instead of writing separate backend loops in Python or Java to update rows one by one, a single SQL query handles millions of changes instantly on the server side. In your Sem 3 SQLite curriculum, you will use these exact subquery techniques to clean up local mobile app storage efficiently.
Summary
Key takeaways
- A subquery is an inner SELECT statement that provides data values to an outer query statement.
- Subqueries can be embedded inside INSERT, UPDATE, and DELETE operations to make data changes dynamic.
- Use single row operators like (=) only when you are certain the subquery returns exactly one value.
- Use multi row operators like IN when the inner subquery can return multiple matching records.
- Do not use the VALUES keyword when inserting rows derived from a subquery statement.
- Memory hook: Inner query gathers facts, Outer query makes the physical modifications!