CODEWITHSUNDEEP
mysqlsql
Arcone
Arcone

Reputation: 3

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?

Upvotes: 0

Views: 65

Answers (2)

Tin Tran
Tin Tran

Reputation: 6202

You basically want to insert products where there doesn't already exist a product with store_id of 6.

INSERT INTO `product_to_store` (product_id,store_id) 
SELECT DISTINCT product_id, 6 as store_id
FROM product_to_store p1
WHERE NOT EXISTS (SELECT 1 FROM product_to_store p2 WHERE p2.store_id = 6
                  AND p2.product_id = p1.product_id)

Upvotes: 1

Matt
Matt

Reputation: 14341

INSERT INTO `product_to_store` (product_id,store_id) 
SELECT DISTINCT p1.product_id, 6 as store_id
FROM
  product_to_store p1
  LEFT JOIN product_to_store x
  ON p1.product_id = x.product_id
  AND x.store_id = 6
WHERE
  x.product_id IS NULL;

http://sqlfiddle.com/#!9/01ac7a

Upvotes: 2

Related Questions