Removing Data from Similar tables

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 2000 Forums

Transact-SQL (2000)

Removing Data from Similar tables

Author

Topic

sherpa99
Starting Member

20 Posts

Posted - 2006-10-18 : 11:43:20

I have a 2 tables that are very similiar -- one holds Inventory, the other is for Items in shopping carts.

I want to write a delete statement that removes items in existing carts when these items are depleted from inventory. Four fields must join up -- (materialNumber, salesOrg, plant, storageLocation)

If this 4-field combination is not in the Inventory table, I want those recoreds deleted from Carts.

How?

The code below, obviously won't work -- I need help. Do I have to resort to a cursor?


Delete from cartItems 
WHERE materialNumber, salesOrg, plant, storageLocation
not in (
SELECT DISTINCT materialNumber, salesOrg, plant, storageLocation
FROM         materialsAvailability 
)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 11:53:09

Before trying this take back up of the table

Delete from cartItems C
WHERE not exists(Select * from materialsAvailability
where materialNumber=C.materialNumber and salesOrg=C.salesOrg and plant=C.plant and storageLocation=C.storageLocation)

Madhivanan

Failing to plan is Planning to fail

sherpa99
Starting Member

20 Posts

Posted - 2006-10-18 : 12:08:54

WOW! We are SO CLOSE

I am getting this error ... it doesn't like the alias ..

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'C'.



Delete from cartItems C  
--Select * from global_cartItems C  -- THIS WORKs GREAT, these are the bad records I want to delete! 
WHERE not exists(Select * from global_product_materialsAvailability
where materialNumber=C.materialNumber and salesOrg=C.salesOrg and plant=C.plant and storageLocation=C.storageLocation)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 12:10:47

Try

Delete C from cartItems C
WHERE not exists(Select * from materialsAvailability
where materialNumber=C.materialNumber and salesOrg=C.salesOrg and plant=C.plant and storageLocation=C.storageLocation)

Madhivanan

Failing to plan is Planning to fail

sherpa99
Starting Member

20 Posts

Posted - 2006-10-18 : 12:28:48

Madhivanan - you are awesome!

THANK YOU -- I am so grateful -- this had me boggled and I really, really appreciated your fast help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 12:32:20

quote:
Originally posted by sherpa99

Madhivanan - you are awesome!

THANK YOU -- I am so grateful -- this had me boggled and I really, really appreciated your fast help.

Thanks for the kind words

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources