Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
join
community creator

How to delete using INNER JOIN

Behzad Ahmad

Overview

In SQL, we use INNER JOIN to join the data of two tables. In some cases, we delete data using an inner join.

To delete the data by using the inner join, the syntax is given below:

Syntax


DELETE firstTable
FROM firstTable INNER JOIN secondTable ON
firstTable.attributeName = secondTable.attributeName
WHERE condition

Suppose we have a table Persons with the following data:

SELECT * FROM Person;

Next, we will create another table named ProjectManager with the following data:

SELECT * FROM ProjectManager;

We use INNER JOIN to delete the ProjectManager above age 5050 and fetch the date_of_birth of the Person to calculate the age at the time of deletion. We will run the following query:

DELETE ProjectManager
FROM  ProjectManager INNER JOIN Person ON
ProjectManager.p_id = Person.person_id
WHERE TIMESTAMPDIFF(YEAR, Person.date_of_birth, '2021-01-23') >= 50;

Explanation

  • Line 1: Delete data from ProjectManager.

  • Line 2–4: We join the ProjectManager and the Persons table based on their relation person_id. Next, we delete the data of the ProjectManager whose age is above 5050.

RELATED TAGS

sql
join
community creator
RELATED COURSES

View all Courses

Keep Exploring