Deleting with multiple joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Deleting with multiple joins

Author

Topic

bigrack
Starting Member

9 Posts

Posted - 2009-07-01 : 10:59:23

Hello,

I am trying to delete a set of data in a table called tblInventoryHistory. In that table are 2 primary fields, WarehouseNumber and ProductNumber that I need to tie into my select statement in order for the delete to work properly.

Here is a select statement of the GOOD data:

Select W.Warehousenumber,W.ProductNumber
from tblproduct P inner join
tblwarehouseproduct W on W.productnumber=P.productnumber Inner Join
tblwarehouse WH on WH.WarehouseNumber = W.WarehouseNumber
Where
WH.IsActive='Y' and
P.ActivityCode not in (-1,3,4,6) and
not (P.ActivityCode in (1,2,5) and onhandquantity=0)

What is the proper syntax for creating this delete statement?

Thanks in advance,
Keith

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 11:51:26

assuming GOOD data is good for deleting and thats what you want to remove from tblInventoryHistory

delete t
from tblInventoryHistory t
join (Select W.Warehousenumber,W.ProductNumber
from tblproduct P inner join
tblwarehouseproduct W on W.productnumber=P.productnumber Inner Join
tblwarehouse WH on WH.WarehouseNumber = W.WarehouseNumber
Where
WH.IsActive='Y' and
P.ActivityCode not in (-1,3,4,6) and
not (P.ActivityCode in (1,2,5) and onhandquantity=0)) a
on t.WarehouseNumber = a.WarehouseNumber and t.ProductNumber = a.ProductNumber

bigrack
Starting Member

9 Posts

Posted - 2009-07-01 : 12:01:55

Ahhh, that makes sense. Actually, the sql statement of GOOD data is the data I want to keep but you have got me on the right path. Thank you!

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 12:06:20

in that case I would use a variation of above query with WHERE NOT EXISTS.
you're welcome.

bigrack
Starting Member

9 Posts

Posted - 2009-07-01 : 12:18:02

You wouldn't just do this?:

...tblwarehouse WH on WH.WarehouseNumber = W.WarehouseNumber
Where NOT (
WH.IsActive='Y' and
P.ActivityCode not in (-1,3,4,6) and
not (P.ActivityCode in (1,2,5) and onhandquantity=0))) a
on t.WarehouseNumber = a.WarehouseNumber and t.ProductNumber = a.ProductNumber

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 12:45:08

yes this should work too.

bigrack
Starting Member

9 Posts

Posted - 2009-07-01 : 12:58:09

Thanks. Wanted to confirm before I ran this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 13:03:55

also you can use left join


delete t
from tblInventoryHistory t
left join (Select W.Warehousenumber,W.ProductNumber
from tblproduct P inner join 
tblwarehouseproduct W on W.productnumber=P.productnumber Inner Join 
tblwarehouse WH on WH.WarehouseNumber = W.WarehouseNumber
Where 
WH.IsActive='Y' and 
P.ActivityCode not in (-1,3,4,6) and 
not (P.ActivityCode in (1,2,5) and onhandquantity=0)) a
on t.WarehouseNumber = a.WarehouseNumber and t.ProductNumber = a.ProductNumber
WHERE a.WarehouseNumber IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources