Jump to content

Mysql Merge Rows?


chibineku
 Share

Recommended Posts

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!

Edited by chibineku
Link to comment
Share on other sites

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.

Edited by justsomeguy
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...