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.
Syntax:
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:
Syntax:
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.
Query:
CREATE DATABASE geeks;
Step 2: Use database
Use the below SQL statement to switch the database context to geeks:
Query:
USE geeks;
Step 3: Table definition
We have two tables named ‘demo_table1‘ and ‘demo_table2‘ in our geek’s database.
Query(demo_table1):
CREATE TABLE demo_table1( NAME VARCHAR(20), AGE int, CITY VARCHAR(10));
Query(demo_table2):
CREATE TABLE demo_table2( NAME VARCHAR(20), AGE int);
Step 4: Insert data into a table
Query(demo_table1):
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');
Query(demo_table2):
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
Query:
SELECT * FROM demo_table1;
Output:
Query:
SELECT * FROM demo_table2;
Output:
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:
Query:
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.
Query:
DELETE FROM demo_table1 WHERE AGE < (SELECT MIN(AGE) FROM demo_table2);
Now check the content of demo_table1
Output:
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).