Mysql Merge Rows?

[chibineku]

I want the option of merging rows in a table. The rows might look like this:id....sessionid....userid....product_id....qty 1....abcdef............3..............1...........22......ghijkl............3..............1...........53.....mnopq...........3..............2...........3I want to merge rows with the same product id, the same user id, regardless of session id, and to have the qty column become a sum total of all the ones being merged. If possible, the most recent session id (the one that the user has currently been assigned) should survive. So, this would be the end result:id....sessionid....userid....product_id....qty 2......ghijkl............3..............1...........73.....mnopq...........3..............2...........3I don't know if this is possible, but if it is, some ideas on the sort of query necessary would be great. Thanks!

[justsomeguy]

That's not one query, you'll need to get all records, loop through them, and do the updates and deletes yourself. The problem with trying to do it in one query is to make sure the old rows got deleted without deleting the new rows.

[chibineku]

I've said it before and I'll say it again: egads, that's complicated. Expect more later - if nothing else, I need the occasional bit of moral support. If you see my code and don't laugh I can press on.

[justsomeguy]

You can make it easier by sorting on user id and product id, at least that way they'll be in order.

[chibineku]

I'm not really entirely 100% sure how sorting/ordering helps me. I've not made enough queries to have had any call for that yet. Perhaps a small example?

[justsomeguy]

If you're counting all records for a certain user ID/product ID, it helps them to be in order. If they aren't in order, you have to keep a list of each ID combo you've seen and keep updating it when you find a new one. If they're in order then if you find a new ID combo you know that the old one is done, and that you're starting a new total.

[chibineku]

Oh I see. Well, I have simplified the design. I've decided, with your prompting, that it isn't necessary to retain basket contents, even for users who are signed in, because unless you have such extensive inventory that people are liable to add hundreds of different items, it isn't really that great a loss, even if users suffer crashes and have to come back and re-add items to their basket. We have only got a select product range so far, very specialized. I will consider revising that and tacking on a keep-alive option later if we expand our range sufficiently or if we feel it's impacting sales.