Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

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:

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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

My Personal Notes arrow_drop_up
Recommended Articles
Page :