SQL查询-根据指定的值多次复制数据

假设在将所有要分配给承运商的产品分配给承运商时,有一个名为table\u with\u product\u list(或list)的表

可以使用插入选择

eg用于多载波(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

对于单载波(5)

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

由于数据库中没有任何信息表明产品应与载体1至4或载体5组合,因此您必须自己声明。在我看来,没有什么可以反对两个独立的 INSERT 声明。您可以使用文本编辑器或Excel或您自己快速编写的程序来构建 VALUES 条款:

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;

或者从上面的第二条语句开始,然后使用以下语句代替第一条查询:

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);