delete with JOIN

[Zelo]

Hallo. Could you help me with my problem. I have 3 tables - Contacts, Orders and OrdersLines. All relationships are one to many. I need to delete a record from contacts. But this contacts could have a lot of orders with products from orderlines. So, when i Deleting a contact i need to delete all orders from table Orders and to delete all order details from OrderLines. How to make this with one query, with JOIN? and all this must work microsoft sql 2005

 

[tehNellie]

If you absolutely want to remove records like this, the easiest way to achieve this would be from within the relationships using cascading deletes, you remove a Contact that has related records and all related records will be deleted as well.

(I've only just started to play with SQL server 2005 express, but you can set a delete rule "cascade" on the relationship in design view".)

[edit] My original SQL here wouldn't work if the tables were related

If you don't want a cascading delete set in your relationships, and you might not because if you accidentally delete a contact with a cascaded delete, you'll remove everything in one fell swoop. you'd need to do something along the lines of:

DECLARE 
  @contactID int,
  @OrderIDs table (orderid int)

SELECT @contactID = ID from contact where Criteria = 'something'

INSERT INTO @Orderids
SELECT ID FROM Orders where ContactID = @ContactID

DELETE FROM Orderlines where OrderID in (SELECT orderid from @Orderids)
DELETE from Orders where ID in (Select orderid from @orderids)
DELETE from Contact where id = @contactid

You could wrap the above in a stored procedure "spDeleteContactInfo" or something and explicitly call that when you know you need/want to delete everything.