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