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

Question

[Table relationships][1]

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?

Answer 1

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

Answer 2

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';

Answer 3

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