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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+<Sample table: agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+Sample table: customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+Sample table: agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
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.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SQL Delete
Next: SQL JOINING
Introduction