在MySQL中执行UPDATE时，在重复键上加上“count”列

Question

I have a count table, and i would like to be able to sum the counts when renaming one of the compound key fields. This is difficult to explain, so let me show you some SQL:

Example table:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`,`type`)
);

Insert some data:

INSERT INTO test VALUES(1, 10, 1);
INSERT INTO test VALUES(2, 20, 3);
INSERT INTO test VALUES(2, 10, 3);
INSERT INTO test VALUES(2, 30, 3);

Query the data:

mysql> SELECT SUM(count) as count FROM test WHERE id = 2;
+-------+
| count |
+-------+
|     9 |
+-------+

mysql> SELECT SUM(count) as count FROM test WHERE type = 10;
+-------+
| count |
+-------+
|     4 |
+-------+

Works very well, is fast and flexible.

Now, I'd like to remap type 10 to type 20

UPDATE test SET type = 20 WHERE type = 10;
Duplicate entry '2-20' for key 'PRIMARY'

Using ON DUPLICATE KEY UPDATE here is invalid. I figure it should be possible with a creative insert, but i'm not sure. Can anyone think of an approach ?

Answer 1

One approach is to "loosen" the PRIMARY KEY, meaning, change it from a PRIMARY (unique) KEY to a non-unique KEY. This will allow for duplicate values, and will allow your UPDATE statement to succeed, such that you will have two (or more) rows with matching (id,type). (Note that this makes updating a single row problematic, so you would likely want to add a new column which can be unique. An AUTO_INCREMENT column would work nicely for that.)

---

If you don't want to do that, then the other approach would be to "combine" the counts for the type 10 and type 20 rows together (for each id) into a type 20 row, set the count for the type 10 rows to zero, and (optionally) remove the redundant type 10 rows in a separate statement.

The statement below "combines" the type 10 and type 20 counts by "mapping" the 10 value to a 20 in the first select.

---

 -- combine count for types 10 and 20 as new count for type 20
 -- and set count to zero for type 10

 INSERT INTO test (id, `type`, `count`)
 SELECT t.id
      , IF(t.`type`=10,20,t.`type`) AS new_type
      , SUM(t.`count`) AS `count`
   FROM test t
  WHERE t.`type` IN (10,20)
  GROUP BY id, new_type
  UNION ALL
 SELECT u.id
      , u.`type`
      , 0 AS `count`
   FROM test u
  WHERE u.`type` = 10
     ON DUPLICATE KEY UPDATE `count` = VALUES(`count`);

 -- remove unnecessary type 10 rows.
 DELETE FROM test WHERE `type` = 10 AND `count` = 0;

---

Note: The IF() expression in the first SELECT is equivalent to:

CASE WHEN t.type = 10 THEN 20 ELSE t.type END

The second select is getting us all the type 10 rows that will need to be updated. We concatenate those two result sets using the UNION ALL operator.

Then we take the combined (concatenated) result set, and run them into an insert. Any place we hit a "duplicate" key, we perform the update action via the ON DUPLICATE KEY clause.

You will likely want to do this in a transaction (if you are using InnoDB) and verify that the first statement completes successfully before executing a DELETE. If the first statement throws an exception (maybe there is a wonky trigger), then you would want to ROLLBACK the transaction. You'd also want to ROLLBACK if the DELETE fails for some reason (perhaps there is a foreign key constraint that would be violated.)

Alternatively, you do not necessarily need to perform the DELETE, you could just leave the type 10 rows with counts of zero.

---

IMPORTANT:

Do NOT implement BOTH of these solutions! Only ONE of them.

The first approach is a schema change, which doesn't require any data changes. That change will allow your UPDATE to succeed.

The second approach requires that the schema remain the same, and depends on the existence (and enforcement) of the UNIQUE KEY on (id,type).