How to use SQL delete with JOIN? [SOLVED]

Getting started with SQL DELETE

SQL delete where join is the combination of SQL JOIN and SQL delete statement. SQL delete statement is used to delete records from a table where as SQL JOIN is used to combine more than one table records. SQL delete with join or delete using inner join is used to remove records from foreign key table based on primary key table condition.

In structured query language (SQL), the DELETE JOIN statement is used to delete rows from multiple tables, or depending on the type of join operation used in the query, all rows from a first table are deleted and the matching rows from a second table.

Syntax

DELETE [target_table]  
FROM [target_table]  
INNER JOIN [join_table]  
ON [target_table].[joining column] = [join_table].[joining column]  
WHERE   [condition]

Here,

• Target_table: specifies the first table name of join from which the records satisfied the where condition will be deleted.
• Join table: Specifies the second table name of join from which matched records with target tanle will be deleted.
• ON [target_table].[joining column] = [join_table].[joining column]  : It specifies the common conditions for joining two tables. A pair of primary keys and foreign keys can be used.

 

Lab Environment

Consider hospital management database with four table patient, doctor, laboratory and bill to perform practical examples.

Patient table

Doctor Table

Laboratory Table

Bill Table

 

Example-1: SQL delete using INNER JOIN on two tables

Write SQL query to remove doctor record that is living in Vadodara city and also remove all lab reports refer by the same doctor

DELETE laboratory
FROM   laboratory INNER JOIN
doctor ON laboratory.doctor_id = doctor.doctor_id
WHERE        (doctor.address = 'Vadodara');

• When we execute above query SQL shows message of one record deleted as  laboratory table contains one  record of doctor id 26 who is living in Vadodra city
• In the above query, SQL delete statement with inner join is used to delete records matched from target table laboratory by retrieving doctor id from doctor table who is living in ‘Vadodara’ city
• Doctor is target table and patient is join table. Where condition will check in doctor table with address column.

 OUTPUT:

To see the output of above query   we need to used SQL select statement

SELECT  lab_no, patient_id, doctor_id, date, amount
FROM  laboratory;

 

Example-2: SQL delete using INNER JOIN on two tables with alias name

We can use alias name of table in SQL delete statement in the place of table name to specify target and join table name from which data to be removed.

Write SQL query to remove patient bill information who is suffering with cancer disease

DELETE  t1
FROM  bill as t1 INNER JOIN
patient AS t2 ON bill.patient_id = t2.patient_id
WHERE        (t2.disease = 'cancer');

• When we execute above query one record will be deleted from bill table as there is only one record of disease as ‘cancer ’.
• In the above query, we have applied SQL delete statement to remove records from bill table based on join condition of two tables.
• We have used SQL inner join with two tables to check for foreign key of same patient id in two tables to delete the record.
• The records with the patient_id same for all two tables will be deleted.

OUTPUT:

To see the output of above query we need to used SQL select statement

SELECT  bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM  bill;

 

Example-3: SQL delete using INNER JOIN on three tables

SQL allows us to join three or more tables by adding another inner join after the first. To delete with inner join on three tables we need to specify table name after delete from which we want to delete records based on two foreign key tables matching column.

Write SQL query to remove patient billing details who is living in ‘althan, surat’ and whose lab report data available in laboratory table.

DELETE FROM bill INNER JOIN
patient ON patient.patient_id = bill.patient_id INNER JOIN
laboratory ON patient.patient_id = laboratory.patient_id
WHERE        (patient.address = 'vadodara');

• When we execute above query two records will be deleted from bill table as the patient_id will address as ‘vadodara’ is 2.
• Table bill contains two records with patient ID 2.
• In the above query, we have applied SQL delete statement to remove records from bill table based on join condition of three tables.
• We have used SQL inner join with three tables to check for foreign key of same patient id in three tables to delete the record.
• The records with the patient_id same for all three tables will be deleted.

OUTPUT:

To see the output of above query   we need to used SQL select statement

SELECT  bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM            bill

 

Summary

This article of delete using INNER JOIN with SQL Server, We have discussed overview of SQL delete where join, syntax to delete using INNER JOIN in SQL, also explain practical examples on SQL delete using INNER JOIN with  two tables, SQL delete using INNER JOIN on two tables with alias name, SQL delete using INNER JOIN on three tables.

 

References

SQL Inner join
SQL delete

 

Read More

Delete using JOIN vs using subquery performance
How can I delete using INNER JOIN with SQL Server?