MySQL: error duplicate record with SELECT ... INSERT INTO with UNIQUE constraint
I have 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 this also has a stock_id value.
The table has 10,000+ rows at the moment, all with the same item_value (1). What I'm trying to do is to duplicate all of its lines twice, both times with the new stock_id value (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;
This works great. It creates 10,000+ new rows with a different stock_id icon, so the uniqueness constraint is not violated even though the product_id for each row already exists.
An example of rows in a table at this point, ordered by product_id (VARCHAR, ugly but necessary), sorry formatting:
22 0032705090062 1 1
10783 0032705090062 2 13
21 0032705090345 1 6
10784 0032705090345 2 0
...
To make each product_id twice, once for each stock_id. Now if I want to create a third stock in a similar way, with the same query as the last time, but replacing "3 AS stock_id", I get this error for the very first line of the product:
"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 as unique as with share_id 1 and 2, no?
Oddly enough, one IS line is created, so there is a new line:
21563 0032705090062 3 5
... but this is the only one out of 10,000+ I'm trying to insert.
What am I missing here? Why does the first SELECT ... INSERT INTO work and the second not?
a source to share
You are selecting from the same table you are inserting into, so the first time you run
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
a source to share
Plain:
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 is failing because there are now 20,000 rows left (not 10,000 as you thought). Adding a where clause ensures that only 10.000 is inserted.
a source to share