Append query with static and SELECT-generated values

[skypanther]

I'm building custom shopping cart application and I'm stuck on a piece of SQL.

I gather the various items people want to buy into a cart table in my database. Once they've gone through the checkout process and are ready to pay, I need to move their cart items to an order items table (for later tracking, managing, etc.).

My tables (abbreviated):

cart
=========
itemID (number)
sessionID (text)
quantity (number)
size (text)
dateAdded (date/time)

orderItems
==========
orderNum (number)
itemID (number)
quantity (number)
size (text)
shipped (yes/no)

orders
=========
orderNum (key / autonumber)
customerID (number)
orderDate (date/time)
orderFilledDate (date/time)
shipping (text)
paidInFull (yes/no)

When planning this out, I thought I'd be able to do an append query, like this:

INSERT INTO orderItems
  (itemID, quantity, size)
SELECT itemID, quantity, size FROM cart

But, I need to get that order number into the orderItems table for each record to be able to associate the orderItems records with the order they belong to (from the orders table). I have that number available within my script as a variable, so I could plug it in if I could do what I want with the SQL. What I wish I could do is this:

INSERT INTO orderItems
  (orderNum, itemID, quantity, size)
VALUES
  (myOrderNumberVariable, SELECT itemID, quantity, size FROM cart)

But of course, that won't work. Is there a simple SQL way of doing this? I've thought up a couple of kludgey workarounds that involve multiple hits on the database. But for performance reasons, I'd rather not do any of them.

For example, I could add sessionID and dateAdded fields to my orderItems table. Then, I could do a double db hit insert kludge like this:

INSERT INTO orderItems
  (itemID, quantity, size, sessionID, dateAdded)
SELECT itemID, quantity, size, sessionID, dateAdded FROM cart

UPDATE orderItems
SET orderNum = myOrderNumberVariable
WHERE sessionID = theSessionID
AND dateAdded = theDateAdded

(I can't just rely on the session ID in case someone places two orders within the 30 minutes or so of the life of their session ID.)

I'd like to do all this in one statement. Any thoughts?

Thanks,
Tim


Skypanther Studios

http://www.skypanther.com

 

[grega]

Not sure of which flavour of SQL you're using, or how exactly your script works, but have you tried

INSERT INTO orderItems
(orderNum, itemID, quantity, size)
SELECT
myOrderNumberVariable, itemID, quantity, size
FROM cart

You syntax will probably be different but the principle may still work.

Greg.

 

[skypanther]

Hi Greg,

Thanks for offering the suggestion. I tried that and it didn't work. But, I did just come up with a solution to my own problem.

I found I can use a subquery to accomplish what I need

INSERT INTO orderItems
(itemID, quantity, size, logoID, color, orderNum)
SELECT itemID, quantity, size, logoID, color, (SELECT orderNum FROM orders
WHERE customerID = customerID AND orderDate = dateOfOrder)
FROM cart
WHERE cart.sessionID = 'sessionID'

By the way, I'm using ColdFusion and (for now) an Access database, to be translated to MySQL when I get around to it.

Tim

Skypanther Studios

http://www.skypanther.com