Removing Old Records..Updating Inventory

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

SQL Server Development (2000)

Removing Old Records..Updating Inventory

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2004-06-08 : 10:47:45

I need to run a stored proc that goes through my shopping cart and updates the Inventory table with the Quantities of items that are older than 15 minutes and then DELETE those items from the CART table. Can this be done without using cursors? I have the basic DELETE below but can anyone elaborate for me please?

Thanks

--Removes all inactive entries from the shopping cart greater than 15 minutes.
DELETE FROM PortalStore_ShoppingCart
WHERE DateDiff(MINUTE, DateCreated, getdate()) > 15

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-08 : 10:56:29

Well doesn't that take care of all your carts at once?

What's the query plan show?

Index scan?

Brett

8-)

jesus4u
Posting Yak Master

204 Posts

Posted - 2004-06-08 : 11:47:16

I checked the query plan but it doesn't help me understand anything. Sorry ;)

However, I came up with this code but it only UPDATES the Inventory table using the first result of the INNER select. The INNER select brings back 2 results.

UPDATE PortalStore_Inventory
SET PortalStore_Inventory.Inventory = (PortalStore_Inventory.Inventory + A.Quantity)
FROM
(SELECT ProductID, Quantity, DateCreated
FROM PortalStore_ShoppingCart
WHERE DateDiff(MINUTE, DateCreated, getdate()) > 15) as A
WHERE (PortalStore_Inventory.ProductID = A.ProductID)

Any ideas?

Alex

quote:
Originally posted by X002548

Well doesn't that take care of all your carts at once?

What's the query plan show?

Index scan?

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-08 : 13:27:44

Can you set up some sample data and show us what you are trying to accomplish with data?

jesus4u
Posting Yak Master

204 Posts

Posted - 2004-06-08 : 14:11:28

thanks but I needed to move on so I did the update in my .NET code instead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources