How to delete from 2 tables using join query in SQL Server

Question

[Table relationships][1]

table **Student**

table **Student_Grade**

Here are the screenshots of my database.

I want to delete all the data from the Student table using the Student_grade table where Grade='FAIL'.

ALL the information of the failed students should be deleted from the database.

Delete from Student
where Student_ID IN (Select Student_ID 
                     from Student_Grade 
                     where Grade = 'FAIL');

Tried this but it's not working. I think data should be deleted from both tables at once because when it deletes from one Student table there is no references for FK in student_grade table.

Please can anyone provide the SQL Server query to do this?

Explore ON DELETE CASCADE: http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server — Vijunav Vastivch, Apr 11 '17 at 03:41

score 2 · Answer 1 · answered Apr 11 '17 at 02:54

you can't delete from 2 tables in one go.

Keep the list of studend_id to be deleted in a temp table and then use that to join to the actual table and delete one at a time.

-- put the list of Fail students in temp table #Fail
Select Student_ID INTO #Fail from Student_Grade where Grade='FAIL'

-- delete from grade table
DELETE g FROM #Fail f INNER JOIN Student_Grade g ON f.Student_ID = g.Student_ID

-- delete from student table
DELETE s FROM #Fail f INNER JOIN Student s ON f.Student_ID = s.Student_ID

score 1 · Accepted Answer · answered Apr 11 '17 at 03:11

I actually like Squirrel's temp approach (+1). It allows for a more complex selection criteria.

That said, if #temp tables are "off-the-table", you can do the following:

Delete A
 From  Student A
 Join  Student_grade B on (A.Student_ID=B.Student_ID)
 Where B.Grade='Fail';

Delete From Student_grade Where Grade='Fail';

score 0 · Answer 3 · edited May 23 '17 at 11:54

There is two way doing it.

first way,use transaction when using more than one DML

begin try
begin tran

-- Child table
Delete From Student_grade Where Grade='Fail';

-- Parent table
Delete A
 From  Student A
 Join  Student_grade B on (A.Student_ID=B.Student_ID)
 Where B.Grade='Fail';



commit TRAN
end try
begin catch
if (@@Trancount>0)
rollback
end catch

Another way is to use

Delete Cascade

,if you want to delete parent table straight away. Delete data with foreign key in SQL Server table

How to delete from 2 tables using join query in SQL Server

3 Answers3