Cart Contents Migration - 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 2000 Forums

Transact-SQL (2000)

Cart Contents Migration

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-22 : 17:15:48

Hi,

The ASP.net IBuySpy store alows an anonymous user to put items in a shopping cart.

If the user then logs in, the items are re-identified as belonging to that user's cart with the following SQL..


CREATE Procedure ShoppingCartMigrate
(
@OriginalCartId nvarchar(50),
@NewCartId nvarchar(50)
)
AS

UPDATE 
ShoppingCart

SET 
CartId = @NewCartId 

WHERE 
CartId = @OriginalCartId
GO

The problem with this is that if the user already has item number 56 in their cart and one of the re-assigned items in the anonymous cart also is an item with PK of 56, the final user cart will have two instances of the item with PK= 56.

How is it possible to avoid this? What needs to happen is that the quantities of multiple instances of an item need to be added together and then surplus instances need to be deleted.

I've seen people solve this buy iterating through the items to be re-assigned and adding each one to the cart individualy by calling the standard stored procedure for doing this however I was wondering if there is a set based method which doesn't use this cursor based approach.

Thanks,

X.Factor

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-10-22 : 19:30:46

Yeah, you can do this with an INSERT INTO...SELECT statement. Just don't put your PK (I'm guessing an identity) into the INSERT or SELECT statement.


INSERT INTO ShoppingCart(Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM ShoppingCart
WHERE CartId = @OriginalCartId

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-22 : 20:36:29

Thanks for your reply.

I don't see how this will solve the problem.

Won't your SQL just re-insert the contents of the original cart back into the table?

The query needs to total the quantity fields of duplicate items, remove duplicate items and update the cart id of the original cart to that of the new cart.

X-Factor.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-22 : 20:49:01

You will need a transaction and error processing but something like

CREATE Procedure ShoppingCartMigrate
(
@OriginalCartId nvarchar(50),
@NewCartId nvarchar(50)
)
AS
update ShoppingCart
SET qty = qty + s2.qty
from ShoppingCart s1
join ShoppingCart s2
on s1.item = s2.item
where s1.CartId = @NewCartId
and s2.CartId = @OriginalCartId

delete ShoppingCart
from ShoppingCart s1
join ShoppingCart s2
on s1.item = s2.item
where s1.CartId = @NewCartId
and s2.CartId = @OriginalCartId

UPDATE ShoppingCart
set CartId = @NewCartId
WHERE CartId = @OriginalCartId
GO

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-10-22 : 20:54:35

Doh, I must have not read one of the paragraphs of your question, the one about the quantity problem.

My Bad!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-23 : 12:55:58

Doh on me too.

I could have gotten this if I had realised that there's never going to be more than two duplicates per item.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources