0

I have two tables, Employee and Address. I want to delete all employees and their associated addresses in Address table. Can we write a single query to do so?

For below query can we use CASCADE keyword?

DELETE FROM Employee
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
manojmore
  • 410
  • 2
  • 8
  • 20
  • 1
    Refer this site https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server – VIGNESH ARUNACHALAM Oct 17 '17 at 05:04
  • 1
    You need to define the `ON CASCADE DELETE` on the **foreign key** relationship between `Address` and `Employee` - you cannot specify it with when issuing a `DELETE` statement – marc_s Oct 17 '17 at 05:21

2 Answers2

0

You can enable set on delete cascade. modify your table sql like this url :

Solution 1

hsyn.ozkara
  • 117
  • 2
  • 11
0

If you don't have foreign keys, easyest way to do this is to use a CTE or a view

with MyCTE as ( select T1.ID asT1ID,T2.ID as T2ID from T1 join T2 on T1.id = T2.id) 
Delete MyCTE where T1ID = ???

or 

Create view MyView 
as 
select T1.ID asT1ID,T2.ID as T2ID from T1 join T2 on T1.id = T2.id

delete MyView where T1ID = ??
RegBes
  • 554
  • 3
  • 11