插入选择多行

yhuiod9q 于 2021-06-17 发布在 Mysql

关注(0)|答案(3)|浏览(408)

嗨，我是mysql新手，我可以成功地插入一行，但我想插入三行，但我一直在想，如果有人可以建议或帮助我，我会非常感谢谢谢你

INSERT INTO earning (id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name),

SELECT x_add1,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address1","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address1'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'

但是我想在多行中插入我分配的不同id和地址
我这样试过，但我知道错了

INSERT INTO earning (id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name),

(id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name),

(id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name)

SELECT x_add1,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address1","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address1'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'

    SELECT x_add2,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address2","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address2'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'

    SELECT x_add13,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address3","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address3'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'

mysql

来源：https://stackoverflow.com/questions/53509965/insert-select-multiple-rows

3条答案

按热度按时间

s5a0g9ez1#

可以使用 UNION 在你的 SELECT 查询。请注意 UNION 是删除重复的行，因此如果要在表中保留这些行，则需要进行更改 UNION 至 UNION ALL .

INSERT INTO earning (id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name)
SELECT x_add1,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address1","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address1'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'
UNION
    SELECT x_add2,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address2","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address2'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'
UNION
    SELECT x_add13,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address3","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address3'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'

赞(0）回复(0）举报 2021-06-18

rmbxnbpk2#

正确的语法是

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

您可以尝试在where子句中添加另一条语句，如下所示

WHERE id = 'x1' and x_address IN (address1, address2, address3)

赞(0）回复(0）举报 2021-06-18

qfe3c7zg3#

要插入多行，请使用以下语法： INSERT INTO tbl_name (column1, column2, column3, ...) VALUES (Value1a, Value2a, Value3a, ...), (Value1b, Value2b, Value2b, ...), (Value1c, Value2c, Value2c, ...);

赞(0）回复(0）举报 2021-06-17

我来回答

插入选择多行

3条答案

相关问题

热门标签

最新问答