CODEWITHSUNDEEP
mysql
Enthu
Enthu

Reputation: 339

SQL Query - Duplicate data more than once based on values assigned

I am trying to duplicate/copy data from "product" table and add new values in "carrier" column.

Example: In "product" table, I have a list of products which I will need to assign with another table, "carrier". There are currently 5 carriers in "carrier" table.

In "product_carrier" table, I would like to create new entries here. Product "sample-1" is assigned to carrier 1, 2, 3 and 4. Product "sample-2" is assigned to carrier 5.

Thus, it becomes as such:

sample-1 | 1

sample-1 | 2

sample-1 | 3

sample-1 | 4

sample-2 | 5

This is the database structure of the e-commerce system that I am currently using to assign carriers.

My tables are -

ps_product: id_product

ps_carrier: id_carrier

ps_product_carrier: id_product, id_carrier_reference

My hunch is that, I will need to update 2 set of data groups. Firstly, products set to carrier (1, 2, 3, 4) and another set of products to assign to carrier (5). I will run 2 set of queries to achieve this.

I have no clue as to execute a duplicate of a product and create insert multiple values for different carriers. Currently, I have thousands of products that need to be assigned accordingly.

I have no idea if this is possible and that if you have any advice, that will be truly appreciated.

Thank you.

Upvotes: 1

Views: 122

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

As there is nothing in the database to indicate whether a product shall be combined with carriers 1 to 4 or with carrier 5, you'll have to state them yourself. In my opinion there is nothing that speaks against two separate INSERT statements. You can use a text editor or Excel maybe or a programm you quickly write yourself to build the VALUES clause:

insert into ps_product_carrier (id_product, id_carrier_reference)
values 
  (1111, 1),
  (1111, 2),
  (1111, 3),
  (1111, 4),
  (3333, 1),
  (3333, 2),
  (3333, 3),
  (2222, 4),
  ... ;

commit;

insert into ps_product_carrier (id_product, id_carrier_reference)
values 
  (2222, 5),
  (4444, 5),
  ... ;

commit;

Or start with the second statement above and then instead of the first query use:

insert into ps_product_carrier (id_product, id_carrier_reference)
select p.id_product, c.id_carrier
from ps_product p
cross join (select id_carrier from ps_carrier where id_carrier in (1,2,3,4)) c
where p.id_product not in 
  (select id_product from ps_product_carrier where id_carrier_reference = 5);

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133410

Assuming you have a table named table_with_product_list (or a list) when you assign all the product you want assign to a carrier

you can use a insert select

eg for multiple carrier (1,2,3,4)

insert into ps_product_reference (id_product, id_carrier_reference)
select table_with_product_list.product_id, t.id_carrier
from table_with_product_list
cross join (
    select id_carrier from ps_carrier
    where id_carrier in  ( 1,2,3,4)

)  t

for single carrier (5) 

insert into ps_product_reference (id_product, id_carrier_reference)
select table_with_product_list.product_id ,  5
from table_with_product_list

Upvotes: 1

Related Questions