SQL Query to DELETE Certain Rows Based on Comparisons with Other Tables
Last Updated :
19 Oct, 2021
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).
Share your thoughts in the comments
Please Login to comment...