MySQL: duplicate entry error with SELECT...INSERT INTO with UNIQUE constraint

Question

I've got the following table:

CREATE TABLE `products_quantity` (
  `id` int(11) NOT NULL auto_increment,
  `product_id` varchar(100) NOT NULL,
  `stock_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `product_id` (`product_id`,`stock_id`),
  KEY `products_quantity_product_id` (`product_id`),
  KEY `products_quantity_stock_id` (`stock_id`)
) ENGINE=MyISAM

product_id is a foreign key to another table, and so is stock_id.

The table has 10 000+ rows at the moment, all with the same stock_id (1). What I'm trying to do is duplicate all of its rows twice, both times with a new stock_id (2 and 3), and a random value for 'quantity'.

Here's the SQL:

INSERT INTO `products_quantity` (product_id, stock_id, quantity)
    SELECT product_id, 2 AS stock_id, FLOOR(-1 + (RAND() * 15)) AS random_quantity FROM products_quantity;

That works fine. It creates 10 000+ new rows with another stock_id, so the uniqueness constraint is not violated although the product_id for each row already exists.

An example of the rows in the table at this point, ordered by product_id (a VARCHAR, ugly but necessary), excuse the formatting:

22   0032705090062 1 1
10783   0032705090062 2 13
21   0032705090345 1 6
10784   0032705090345 2 0
...

That's every product_id twice, once for each stock_id. Now, if I want to create a third stock in a similar fashion, with the exact same query as last time but substituting '3 AS stock_id', I get this error for the very first product row:

"Duplicate entry '0032705090062-3' for key 2"

Suddenly, the uniqueness constraint is supposedly violated, although the combination of product_id 0032705090062 and stock_id 3 is just as unique as with stock_id 1 and 2, no?

Funnily enough, the single row IS created, so there is a new row:

21563    0032705090062  3 5

...but that is the only one of the 10 000+ that I'm trying to insert.

What am I missing here? Why does the first SELECT...INSERT INTO work, but the second doesn't?

Answer 1

You're selecting from the same table you're inserting to, so the first time it grabs

22      0032705090062   1       1
21      0032705090345   1       6

then inserts

10783   0032705090062   2       13
10784   0032705090345   2       0

However when you run it again it will:

GET     22      0032705090062   1       1
INSERT  21563   0032705090062   3       5
GET     10783   0032705090062   2       13
INSERT          0032705090062   3   <-- oops, already exists

You just need to add WHERE stock_id = 1 to your SELECT

Answer 2

Simple:

INSERT INTO `products_quantity` (product_id, stock_id, quantity)
    SELECT 
      product_id, 
      3 AS stock_id, 
      FLOOR(-1 + (RAND() * 15)) AS random_quantity 
    FROM 
      products_quantity;
    WHERE
      stock_id = 1  /* !!!!! */

Your second insert fails because there are 20.000 rows now (not 10.000, like you've thought). Adding the where clause makes sure that only 10.000 are inserted.