Delete statement with JOIN in SQL Server 2005

We often need to delete data from table based on JOIN.  In that case, what we do is, try to execute SELECT query with JOIN, we find and confirm the records we want to delete and then remove SELECT statement and write DELETE instead. But this will not work in case of JOIN. We need to give table alias explicitly to tell SQL Server Engine that what table we need to include in delete. Have a look at following case.

–create table1 for demo
create table Orders1
(
      OrderID INT not null constraint pk_ordid primary key,
      orderdate datetime
)
–create table2 for demo
create table orderDetails1
(
OrderDetailsID int not null,
OrderID INT not null constraint fk_ordid references Orders1(OrderID)
)
–insert records
insert into orders1
select 1,getdate() union all
select 2,getdate() union all
select 3,getdate() union all
select 4,getdate()
insert into orderdetails1
select 1,3 union all
select 2,2 union all
select 3,3 union all
select 4,1 union all
select 5,2
GO
–try to select records which you want to delete
select o.OrderID,o.orderDate,od.OrderDetailsID
from Orders1 o inner join orderdetails1 od
on o.orderid=od.orderid
order by o.orderid
–now try to delete records by removeing
–SELECT statement and put DELETE instead.
–it will not work, as SQL Engine will be confused,
–what to delete, orders1? or orderdetails1?
delete
–select o.OrderID,o.orderDate,od.OrderDetailsID
from Orders1 o inner join orderdetails1 od
on o.orderid=od.orderid
–order by o.orderid


–here is the solution, you have to specify
–I need to delete from od (OrderDetails)
delete od
from Orders1 o inner join orderdetails1 od
on o.orderid=od.orderid
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com