3
\$\begingroup\$

I have the following query which updates the quantity when there are duplicate items and users (there should only ever be 2 duplicates), then deletes the row which doesn't get updated...

UPDATE cart_items t
  JOIN (
    SELECT MinID, b.itemQuantity LatestName
    FROM cart_items b
    JOIN (
      SELECT MIN(id) MinID, MAX(id) MaxID
      FROM cart_items
      GROUP BY account_id, itemNumber
      HAVING COUNT(*) > 1
    ) g ON b.id = g.MaxID
  ) rs ON t.id = rs.MinID
SET t.itemQuantity = t.itemQuantity+LatestName;

DELETE `cart_items` 
FROM `cart_items`
LEFT OUTER JOIN (
   SELECT MIN(`id`) as `id`, `account_id`, `itemNumber`
   FROM `cart_items` 
   GROUP BY `account_id`, `itemNumber`
) as `KeepRows` ON
   `cart_items`.`id` = `KeepRows`.`id`
WHERE
   `KeepRows`.`id` IS NULL;

However I'm pretty sure this is not the best way to achieve this.

Any suggestions?

Here's the schema for the table:

CREATE TABLE `cart_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) unsigned NOT NULL,
  `itemNumber` int(11) NOT NULL,
  `itemQuantity` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8;

INSERT INTO `cart_items` (`id`, `account_id`, `itemNumber`, `itemQuantity`)
VALUES
    (54, 10, 49, 1),
    (55, 10, 49, 2);
\$\endgroup\$
  • \$\begingroup\$ Out of curiosity, are there dependencies on this table that require a 1:1 ratio or rows:itemNumber within an account_id group? I ask because those dependencies might do better mapped to a view that groups them. Beyond that, this approach is a band-aid for the issue where the application isn't looking up an itemNumber in the cart within the account_id scope when doing an insert, and switching action to an update when present. Having said that, which approach (including your original question) sounds like a better one in your scenario? \$\endgroup\$ – Jaaz Cole Sep 29 '14 at 18:30
  • \$\begingroup\$ Hi, the reason I'm having to do this is because users can add items to their basket without being logged in (a temporary account set up for the user), then I want to add these items to their existing basket after they log in. If any items are merged because they appear in both baskets, I'll flag it up with a message to the end user so they double check the quantities before checking out. Hope this makes sense. \$\endgroup\$ – Tom Sep 30 '14 at 8:02
  • \$\begingroup\$ Would it not make sense to alter the add to cart functionality and the table? Check this out...dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html then add account_id and itemNumber to a unique index, and the code in the first example should update the itemQuanity column on its own. Would this work in the context of the broader application? \$\endgroup\$ – Jaaz Cole Dec 11 '14 at 19:57
2
\$\begingroup\$

A safer way than this would be to do something like

CREATE TABLE `cart_items_nodupes` (
  `id` int(11) NOT NULL,
  `account_id` int(11) unsigned NOT NULL,
  `itemNumber` int(11) NOT NULL,
  `itemQuantity` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (account_id, itemNumber)
) ENGINE=InnoDB CHARSET=utf8;

This creates a new table that maintains a unique index on account_id and itemNumber, so you'll never have a duplicate.

Then

INSERT INTO `cart_items_nodupes`
SELECT * FROM `cart_items`
ON DUPLICATE KEY UPDATE itemQuantity = itemQuantity + VALUES(itemQuantity)

Or

INSERT INTO `cart_items_nodupes`
SELECT MIN(id) AS id, account_id, itemNumber, SUM(itemQuantity) AS itemQuantity
  FROM `cart_items`
  GROUP BY account_id, itemNumber

Either of these will insert all the values from the old table into the new table, combining those with the same account_id and itemNumber.

And

ALTER TABLE cart_items_nodupes
  DROP PRIMARY KEY,
  MODIFY id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ADD UNIQUE INDEX uc_account_item (account_id, itemNumber)
ALTER TABLE cart_items_nodupes AUTO_INCREMENT = 56
RENAME cart_items TO cart_items_original, cart_items_nodupes TO cart_items

This creates a new table that ensures there are no duplicates and then copies all the values from the original table. Then it sets up the primary key so as to continue from the original table. Finally, it renames the tables so that the new table takes the place of the old table. The 56 was taken from your table definition -- modify as necessary. Then you can just continue using the table as normal (after you check the table definition and rows).

Note that you need to stop updating the table while doing this. You can sort of make the INSERT ON DUPLICATE KEY UPDATE work by saying WHERE id < 56 (again, 56 is based on the table definition -- adjust as necessary). If new rows are added, you can get them with WHERE id >= 56. But you should stop updates before altering the table indexes and renaming the tables.

An advantage of doing things this way is that if the data is wrong after the inserts, you can always drop the table and try again. Your original data is still there, untouched. And you can test it before doing it for real.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.