Is it possible to delete records from two tables with single DELETE statement.
Thanks,
Regards,
Nakkeeran Rengasamy
You can't do it directly.
You have to use the INSTEAD OF TRIGGER (works on Both 2000 & 2005) or OUTPUT clause (if you use SQL Server 2005).
Using Output Clause:
Code Snippet
Create Table #A (
[AId] int ,
[Name] Varchar(100)
);
Insert Into #A Values('1','One');
Insert Into #A Values('2','Two');
Insert Into #A Values('3','Three');
Create Table #B (
[BId] int ,
[AId] int ,
[Desc] Varchar(100)
);
Insert Into #B Values('1','1','SomeText 1.1');
Insert Into #B Values('2','1','SomeText 1.2');
Insert Into #B Values('3','2','SomeText 2.1');
Insert Into #B Values('4','3','SomeText 3.1');
DECLARE @.MyTableVar table (ID int)
Delete #A Output Deleted.AID into @.MyTableVar Where AID=1
Delete From #B Where AId in (Select ID from @.MyTableVar)
