SQL Delete records using subqueries

SQL Deleting records with subqueries

In this page, we are going to discuss, how SUBQUERIES (A SELECT statement within another SELECT statement can be used as a subquery )along with SQL DELETE command can be used to perform a deletion.

Sample tables associated with this page have shown bellow:

Sample table: customer1

Sample table: agents

Sample table: customer

Sample table: agent1

Sample table: orders

Example:

To remove rows from the table 'customer1' with following conditions -

1. 'agent_code' should be any 'agent_code' from 'agents' table which satisfies the condition bellow :

2. 'working_area' of 'agents' table must be 'London',

the following SQL statement can be used:

SQL Code:

-- This SQL code deletes records from the 'customer1' table where the 'agent_code' column value matches any agent code from a subquery result.
DELETE FROM customer1 -- This line specifies the action of deleting records from the 'customer1' table.
WHERE agent_code=ANY( -- This line specifies the condition for deletion, selecting only rows where the 'agent_code' column value matches any value returned by the subquery.
SELECT agent_code FROM agents -- This subquery selects the 'agent_code' column from the 'agents' table.
WHERE working_area='London'); -- This subquery filters the 'agents' table to only include rows where the 'working_area' column value is 'London'.

Explanation:

• This SQL code performs a deletion operation on a database table named 'customer1'.


• It removes records from the table where the 'agent_code' column value matches any agent code associated with agents working in London.


• The operation is based on a subquery that selects agent codes from the 'agents' table where the working area is 'London'. The main deletion query then deletes records where the 'agent_code' matches any of these selected codes.


• The 'DELETE FROM' statement is used to remove records from the specified table.


• The 'WHERE' clause is used to specify the condition that determines which records will be deleted. In this case, records where the 'agent_code' column value matches any agent code associated with agents working in London will be deleted. The 'ANY' keyword is used to compare the 'agent_code' column value with multiple values returned by the subquery.

Output:

SQL delete records using subqueries with alias

In this page, we are going to discuss, how table aliases( when two or more tables used in a query, then alias makes it easy to read and write with a short name which comes after the table name after the FROM keyword) can be used with SUBQUERIES (A SELECT statement within another SELECT statement can be used as a subquery ), and with the help of subqueries SQL DELETE command can be used to delete records.

Example:

To remove rows from the table 'agent1' with following conditions -

1. 'da' and 'cu' are the aliases for the table 'agent1' and 'customer'

2. check the existence of the subquery is true or false. which satisfies the condition bellow :

3. 'grade' of 'customer' table must be 3,

4. 'agent_code' of 'agent1' table and 'agent_code' of 'customer' table should not be same,

the following SQL statement can be used:

SQL Code:

-- This SQL code deletes records from the 'agent1' table based on a condition involving data from another table 'customer'.
DELETE FROM agent1 da -- This line specifies the action of deleting records from the 'agent1' table, aliasing it as 'da'.
WHERE EXISTS( -- This line specifies the condition for deletion, based on the existence of rows returned by the subquery.
SELECT * FROM customer cu -- This subquery selects all columns from the 'customer' table, aliasing it as 'cu'.
WHERE grade=3 -- This subquery filters the 'customer' table to only include rows where the 'grade' column value is 3.
AND da.agent_code<>cu.agent_code); -- This subquery further filters the result to include only rows where the 'agent_code' column value of 'da' is not equal to the 'agent_code' column value of 'cu'.

Explanation:

• This SQL code performs a deletion operation on the 'agent1' table based on a condition involving data from the 'customer' table.


• It removes records from 'agent1' where there exists a record in the 'customer' table with grade 3 and a different agent code.


• The operation uses a subquery to check for the existence of such records in the 'customer' table.


• The 'DELETE FROM' statement is used to remove records from the specified table ('agent1').


• The 'WHERE EXISTS' clause is used to specify the condition that determines which records will be deleted. In this case, records from 'agent1' will be deleted if there exists a record in 'customer' with grade 3 and a different agent code.

Output:

SQL delete records using subqueries with alias and IN

In this page we are going to discuss, how rows can be removed from a table by SQL DELETE statement with the use of IN operator and SUBQUERIES.

Example:

To remove rows from the table 'agent1' with following conditions -

1. 'da' and 'cu' are the aliases of 'agent1' and 'customer' table,

2. check the number 3 is in the result of the subquery which satisfies the condition bellow :

3. 'agent_code' of 'agent1' table and 'agent_code' of 'customer' table should not be same,

the following SQL statement can be used:

SQL Code:

-- This SQL code deletes records from the 'agent1' table based on a condition involving data from another table 'customer'.
DELETE FROM agent1 da -- This line specifies the action of deleting records from the 'agent1' table, aliasing it as 'da'.
WHERE 3 IN( -- This line specifies the condition for deletion, checking if the value 3 is present in the result set returned by the subquery.
SELECT grade FROM customer cu -- This subquery selects the 'grade' column from the 'customer' table, aliasing it as 'cu'.
WHERE agent1.agent_code<>customer.agent_code); -- This subquery filters the 'customer' table to only include rows where the 'agent_code' column value of 'agent1' is not equal to the 'agent_code' column value of 'customer'.

Explanation:

• This SQL code performs a deletion operation on the 'agent1' table based on a condition involving data from the 'customer' table.


• It removes records from 'agent1' where the value 3 is present in the 'grade' column of 'customer' with a different agent code.


• The operation uses a subquery to fetch 'grade' values from the 'customer' table.


• The 'DELETE FROM' statement is used to remove records from the specified table ('agent1').


• The 'WHERE IN' clause is used to specify the condition that determines which records will be deleted. In this case, records from 'agent1' will be deleted if the value 3 is present in the 'grade' column of 'customer' with a different agent code.

Output:

SQL delete records using subqueries with alias and MIN

In this page, we are going to discuss, how rows can be removed from a table by SQL DELETE statement along with the SQL MIN() function.

Example:

To remove rows from the table 'agent1' with following conditions -

1. 'orders' table used as alias 'a' and alias 'b',

2. 'agent_code' of 'agent1' should be within the 'agent_code' in alias 'a' which satisfies the condition bellow :

i) 'ord_amount' of alias 'a' must be equal to the minimum 'ord_amount' of alias 'b' which satisfies the condition bellow :

a) 'ord_date' of alias 'a' and alias 'b' must be equal,

the following SQL statement can be used :

SQL Code:

-- This SQL code deletes records from the 'agent1' table based on a condition involving data from another table 'orders'.
DELETE FROM agent1 -- This line specifies the action of deleting records from the 'agent1' table.
WHERE agent_code IN -- This line specifies the condition for deletion, checking if the 'agent_code' column value is present in the result set returned by the subquery.
(SELECT agent_code FROM orders a -- This subquery selects the 'agent_code' column from the 'orders' table, aliasing it as 'a'.
WHERE ord_amount= -- This subquery filters the 'orders' table to only include rows where the 'ord_amount' column value matches a specific condition.
(SELECT MIN(ord_amount) FROM orders b -- This nested subquery selects the minimum 'ord_amount' value from the 'orders' table, aliasing it as 'b'.
WHERE a.ord_date=b.ord_date)); -- This nested subquery further filters the result to only include rows where the 'ord_date' column values of 'a' and 'b' match.

Explanation:

• This SQL code performs a deletion operation on the 'agent1' table based on a condition involving data from the 'orders' table.


• It removes records from 'agent1' where the 'agent_code' column value is present in the result set of agents associated with orders having the minimum order amount on a specific date.


• The operation uses nested subqueries to determine the agents associated with the minimum order amount on a given date.


• The 'DELETE FROM' statement is used to remove records from the specified table ('agent1').


• The 'WHERE IN' clause is used to specify the condition that determines which records will be deleted. In this case, records from 'agent1' will be deleted if the 'agent_code' column value is present in the result set of agents associated with orders having the minimum order amount on a specific date.

Output:

SQL delete records using subqueries with alias and MIN and COUNT

In this page, we are going to discuss, how rows can be removed from a table by SQL DELETE statement along with the SQL MIN() and COUNT() function.

Example:

To remove rows from the table 'agent1' with following conditions -

1. 'orders' table used as alias 'a' and alias 'b'

2. 'agent_code' of 'agent1' should be within the 'agent_code' in alias 'a' which satisfies the condition bellow:

i) 'ord_amount' of alias 'a' must be equal to the minimum 'ord_amount' of alias 'b' which satisfies the condition bellow :

a) 'ord_date' of alias 'a' and alias 'b' must be equal

ii) the number 1 should be less than the number of 'ord_num' form alias 'b' which satisfies the condition bellow :

a) 'ord_date' of alias 'a' and alias 'b' must be equal,

the following SQL statement can be used :

SQL Code:

-- This SQL code deletes records from the 'agent1' table based on a complex condition involving data from another table 'orders'.
DELETE FROM agent1 -- This line specifies the action of deleting records from the 'agent1' table.
WHERE agent_code IN( -- This line specifies the condition for deletion, checking if the 'agent_code' column value is present in the result set returned by the subquery.
SELECT agent_code FROM orders a -- This subquery selects the 'agent_code' column from the 'orders' table, aliasing it as 'a'.
WHERE ord_amount= -- This subquery filters the 'orders' table to only include rows where the 'ord_amount' column value matches a specific condition.
(SELECT MIN(ord_amount) FROM orders b -- This nested subquery selects the minimum 'ord_amount' value from the 'orders' table, aliasing it as 'b'.
WHERE a.ord_date=b.ord_date) -- This nested subquery further filters the result to only include rows where the 'ord_date' column values of 'a' and 'b' match.
AND 1<( -- This condition checks if the count of orders with the same order date is greater than 1.
SELECT COUNT(ord_num) FROM orders b -- This subquery counts the number of orders with the same order date, aliasing it as 'b'.
WHERE a.ord_date=b.ord_date)); -- This subquery further filters the result to only include rows where the 'ord_date' column values of 'a' and 'b' match.

Explanation:

• This SQL code performs a deletion operation on the 'agent1' table based on a complex condition involving data from the 'orders' table.


• It removes records from 'agent1' where the 'agent_code' column value is present in the result set of agents associated with orders having the minimum order amount on a specific date and where there is more than one order on that date.


• The operation uses nested subqueries to determine the agents associated with the minimum order amount and where the count of orders on the same date is greater than 1.


• The 'DELETE FROM' statement is used to remove records from the specified table ('agent1').


• The 'WHERE IN' clause is used to specify the condition that determines which records will be deleted. In this case, records from 'agent1' will be deleted if the 'agent_code' column value is present in the result set of agents associated with orders having the minimum order amount on a specific date and where there is more than one order on that date.

Output:

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL Delete
Next: SQL JOINING Introduction