Open In App

SQL Query to DELETE Certain Rows Based on Comparisons with Other Tables

Improve
Improve
Like Article
Like
Save
Share
Report

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).


Last Updated : 19 Oct, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads