CODEWITHSUNDEEP
sql-server
saad rafique
saad rafique

Reputation: 3

How to delete the data in two rows in different table in SQL Server 2012?

How to delete the data in a row in table 1 whose primary key is using as a foreign key in 2nd table.. I am facing the problem here 

delete from ASSIGNMENT 
where proj_num=18
delete from PROJECT 
where proj_num=18

proj_num is the primary key in project and foreign key in assignment.

How to do this task in one delete query ?

Upvotes: 0

Views: 41

Answers (2)

CurseStacker
CurseStacker

Reputation: 1079

Or if you don't have access to the table design you may use this.

DELETE proj
FROM Project AS proj INNER JOIN Assignment AS assn
ON proj.proj_num = asn.proj_num
WHERE proj_num = 18

Upvotes: 0

pcofre
pcofre

Reputation: 4076

You can create/edit the relationship between the two tables in order to set the delete behaviour to ON DELETE CASCADE, then you can just delete the proj_num=18 from the PROJECT table, and it will automatically delete the related records from the ASSIGNMENT table. Hope it works for you. 

ALTER TABLE ASSIGNMENT
ADD CONSTRAINT fk_proj
    FOREIGN KEY (proj_num)
    REFERENCES PROJECT (proj_num)
    ON DELETE CASCADE;

Upvotes: 1

Related Questions