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?

Greg · Accepted Answer · 2009-05-22 09:28:55Z

2

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

answered May 22, 2009 at 9:28

Greg

323k55 gold badges378 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tomalak · Accepted Answer · 2009-05-22 09:39:44Z

1

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.

edited May 22, 2009 at 9:39

answered May 22, 2009 at 9:30

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related