Merging totals from rows with duplicate IDs

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);

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. 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.

StackExchange Code Review Q#63665, answer score: 2