Arcone Arcone - 2 months ago 6
MySQL Question

Insert multiple rows from one column in a table to the same column from the same table

Okay, so I have the following structure in my

product_to_store
table:

+----------------------+--------------------+
| product_id - int(11) | store_id - int(11) |
+----------------------+--------------------+
+----------------------+--------------------+
| 1000 | 0 |
| 1000 | 6 |
| 1005 | 0 |
| 1010 | 0 |
| 1010 | 6 |
...


Basically, I need to have a
store_id
of value
6
for every
product_id
. For example, the product (
product_id
) with ID
1005
only has a
store_id
record of
0
. I want it to have another record/row where
product_id
is equal to
6
. Products with ID
1000
and
1010
are what they should be like (they have a record of
store_id
that is equal to
6
).

I tried to run the following query in order insert a new row where only
product_id
is set:

INSERT INTO `product_to_store`
(product_id) SELECT `product_id`
FROM `product_to_store`
WHERE product_id != 6


And then consider running another query to update all rows where
store_id
is
null
with the value of
6
. However, I get:

#1062 - Duplicate entry '1000-0' for key 'PRIMARY'
.

Any way in which I can accomplish this without having to use a loop in PHP or something rather unpractical?

Answer
INSERT INTO `product_to_store` (product_id,store_id) 
SELECT DISTINCT `product_id`, 6 as store_id
FROM `product_to_store` WHERE store_id != 6

Just add the constant 6 as a column to your query. and reference store_id in the column list of the insert statement.