merging rows?

[NancyJ]

Is it possible to merge rows in SQL?

eg. I have rows that contain a product ID, sessionID, Colour, Size and quantity.
Is it possible in SQL to merge the rows so that if a row contains the same product ID, session ID, colour and size then the rows would be merge adding the quantity fields?

I'm using this to display the merged rows but is there a way to modify this to permanently merge the rows... the reason I want to do this is I want to be able to modify the quantities eg. the form shows 3 as the quantity but that might be made up of 2 rows of a 2 and a 1 so I would only want to update one of the rows with that productID not both.


SELECT DISTINCT
Store_tblCart.fldProductID, Store_tblCart.fldSize,
Store_tblCart.fldColour, Store_tblCart.fldPersonalisation,
SUM(Store_tblCart.fldQuantity) AS fldQuantity,
Store_tblProducts.fldTitle, Store_tblProducts.fldPrice,
SUM(Store_tblProducts.fldPrice * fldQuantity)
AS Subtotal
FROM Store_tblCart INNER JOIN
Store_tblProducts ON
Store_tblCart.fldProductID = Store_tblProducts.fldProductID
WHERE Store_tblCart.fldSessionID = '993063358'
GROUP BY Store_tblCart.fldProductID, Store_tblCart.fldSize,
Store_tblCart.fldColour, Store_tblCart.fldPersonalisation,
Store_tblProducts.fldTitle, Store_tblProducts.fldPrice


so my question is basically, is there a way to update only 1 row that matches the criteria or merge the rows so that I can use the ID field to update them?

[Kid Charming]

You should be able to create a new table with the same fields, and do an INSERT...SELECT to get all the information from your original table with the same type of select you're getting your info with now.

[NancyJ]

I dont really want to be creating a new table every time someone updates their shopping cart.
Instead I decided just to delete the cart and reinsert it using the information from the form and that works fine. I'm sure theres a more effecient solution but this one works ok.