Deleting data by comparing to another table

[Bill]

I have an entry form allowing customers to enter up to 15 skus (product
id) at a time, so they can make a multiple order, instead of entering
one sku, then submitting it, then returing to the form to submit the
second one, and so forth.

From time to time, the sku they enter will be wrong, or discontiued, so
it will not submit an order.

Therefore, when they are done submitting their 15 skus through the order
form, I want a list showing them all of those skus that came back blank,
or were not found in the database.

I'm doing this by creating two tables. A shopping cart, which holds all
the skus that were returned, and a holding table, that holds all the
skus that were submitted. I want to then delete all the skus in the
holding page that match the skus in teh cart (because they are good
skus) which will then leave the unmatched skus in the holding table.
I'll then scroll out the contents of the holding table, to show them the
skus that were not found in the database.
(confused yet?)
So what I want to do is have some sql that will delete from the holding
table where the sku = the sku in the cart. I've tried writing this, but
it dosn't work.

I tiried this delete from holding_table where sku = cart.sku

I was hoping this would work, but it dosn't. Is there a way for me to do
this?

Thanks!

Bill
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

Let me suggest that you have the wrong design to start with. Creating
shopping cart tables and holding tables for each user is not a very
effective design and it doesn't make sense in relational terms.

Create a table for all your shopping carts with an ID for each user/session:

CREATE TABLE ShoppingCarts (cart_id INTEGER, sku INTEGER NOT NULL /*
REFERENCES Products (sku) */, PRIMARY KEY (cart_id,sku))

I would have thought it made sense to validate the SKUs as they are entered
which is what the commented FK constraint does in the above code. If you
want to post-validate the SKUs, leave the FK constraint out and display the
invalid entries with this query (@cart_id is the id for this user/session):

SELECT C.sku AS Invalid
FROM ShoppingCarts AS C
LEFT JOIN Products AS P
ON C.sku=p.sku
WHERE C.cart_id = @cart_id AND P.sku IS NULL

You can delete the invalid SKUs like this:

DELETE
FROM ShoppingCarts
WHERE cart_id = @cart_id
AND NOT EXISTS
(SELECT *
FROM Products
WHERE sku = ShoppingCarts.sku)

--
David Portas
------------
Please reply only to the newsgroup
--

[Bill]

AWESOME, David! Thank you.

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!