One DELETE sql statement to delete from two tables

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

I think Triggers may help for this scenario...

I am not sure about one delete statement to delete from two tables.

Thanks

Sreekanth

|||

If both tables has the relation (child-master with foreign key), then you can use the ON DELETE CASCADE on your Primary Key (Master) constraint.

Code Snippet

Create MasterTable

(

Id int Primary Key On Delete Cascade,

..

..

)

Create Childtable

(

Master_TableId int References MasterTable(Id)

..

..

..

)

If the tables doesn’t have any relation (logically bound), then you can use the Trigger to delete the values from the tables – using DELETED special table.

Code Snippet

Create trigger trigger_name On FirstTable For Delete

As

Begin

Delete From SecondTable Where ID in (Select ID from DELETED)

End;

Go

Delete From FirstTable

If you use SQL Server 2005, then you can use the OUTPUT clause, to get the deleted values from the current table, and supply those deleted values to remove the records from other table.

Code Snippet

Declare @.DeletedIds Table

(

Id int

)

Delete FirstTable OUTPUT DELETED.ID INTO @.DeletedIds Where <condition>;

Delete From SecondTable Where ID in (Select Id from @.DeletedIds);

|||

More information may be required for answering this. The simple answer is, no, you cannot "technically" do this. Any method that you can simulate this will technically be multiple SQL operations. Triggers, ON CASCADE constraints, etc.

There is really no need for one statement to delete from multiple tables, the key for your needs is probably that you want to make sure that if one statement completes, then the other statement completes. This is referred to as an atomic operation, and is managed by Transactions. So, simplistically (you need error handling/messaging for sure) in the following:

BEGIN TRY

BEGIN TRANSACTION

DELETE 1

DELETE 2

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

If DELETE 2 fails, but DELETE 1 succeeds, DELETE 1 will be undone.

sql