SQL Query to DELETE Certain Rows Based on Comparisons with Other Tables
In this article, we will see, how to delete certain rows based on comparisons with other tables. We can perform the function by using a subquery in place of the condition in WHERE Clause. A subquery is a query that appears inside another query. It can also be called a nested query.
SELECT * FROM table_name WHERE column_name= ( SELECT column_name FROM table_name);
A SELECT query is written after the WHERE clause is the subquery.
To delete the row from the table:
DELETE FROM table_name WHERE condition;
Now, for the demonstration follow the below steps:
Step 1: Create a database
we can use the following command to create a database called geeks.
CREATE DATABASE geeks;
Step 2: Use database
Use the below SQL statement to switch the database context to geeks:
Step 3: Table definition
We have two tables named ‘demo_table1‘ and ‘demo_table2‘ in our geek’s database.
CREATE TABLE demo_table1( NAME VARCHAR(20), AGE int, CITY VARCHAR(10));
CREATE TABLE demo_table2( NAME VARCHAR(20), AGE int);
Step 4: Insert data into a table
INSERT INTO demo_table1 VALUES ('Romy',23,'Delhi'), ('Pushkar',23,'Delhi'), ('Nikhil',24,'Punjab'), ('Rinkle',23,'Punjab'), ('Samiksha',23,'Banglore'), ('Ashtha',24,'Banglore'), ('Satish',30,'Patna'), ('Girish',30,'Patna'), ('Ram', 20 , 'Patna'), ('Raj', 12, 'Delhi');
INSERT INTO demo_table2 VALUES ('Fanny',25 ), ('Prem', 30), ('Preeti',21 ),('Samita',32);
Step 5: View the content
Execute the below query to see the content of the table
SELECT * FROM demo_table1;
SELECT * FROM demo_table2;
Step 6: Delete rows based on comparison using subquery
For the demonstration, let’s delete rows from demo_table1 whose AGE is less than the minimum AGE value of demo_table2
To get the minimum AGE value of demo_table2:
SELECT MIN(AGE) FROM demo_table2;
Now, we will use the above query as subquery to delete the row having values less than the value obtained from the query.
DELETE FROM demo_table1 WHERE AGE < (SELECT MIN(AGE) FROM demo_table2);
Now check the content of demo_table1
We can see in the image that 2 entries are deleted now that is (‘Ram’, 20 , ‘Patna’), (‘Raj’, 12, ‘Delhi’) as both the entries has AGE value less than 21(minimum age value from demo_table2).