mysql - 这个子查询有什么问题

Question

我正在尝试运行子查询，以将从另一个数据库的表收集的数据插入到数据库的表中。当我运行此查询时，我收到以下错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use

near 'SELECT * FROM campaign_traffic_db.campaign_merge WHEREadform_clientID = 135456 ' at line 1

INSERT
INTO
  135456_1257994024297010.campaign_merge(
    adform_client,
    adform_clientID,
    adform_campaign,
    adform_campaignID,
    adform_campaignStartDate,
    adform_campaignEndDate,
    adform_campaignCurrency,
    adform_cost,
    adform_clicks,
    adform_ctr,
    adform_impression,
    fb_client,
    fb_clientID,
    fb_campaign,
    fb_campaignID,
    fb_campaignStartDate,
    fb_campaignEndDate,
    fb_cost,
    fb_clicks,
    fb_impression,
    created_at,
    updated_at
  )
VALUES (
SELECT
  *
FROM
  campaign_traffic_db.campaign_merge
WHERE
  adform_clientID = '135456' AND fb_clientID = '1257994024297010')

Answer 1

作为我上面评论的后续，语法:

INSERT INTO <table> (f1, f2, f3)
VALUES (SELECT f1, f2, f3 FROM <table2>);

不会工作。您可以使用 VALUES() 或提供一个 SELECT 语句来返回所需的字段以匹配您的 INSERT 字段列表。

INSERT INTO 135456_1257994024297010.campaign_merge
(
    adform_client,
    adform_clientID,
    adform_campaign,
    adform_campaignID,
    adform_campaignStartDate,
    adform_campaignEndDate,
    adform_campaignCurrency,
    adform_cost,
    adform_clicks,
    adform_ctr,
    adform_impression,
    fb_client,
    fb_clientID,
    fb_campaign,
    fb_campaignID,
    fb_campaignStartDate,
    fb_campaignEndDate,
    fb_cost,
    fb_clicks,
    fb_impression,
    created_at,
    updated_at
)
SELECT * FROM campaign_traffic_db.campaign_merge
WHERE adform_clientID = '135456' AND fb_clientID = '1257994024297010';

假设您的 campaign_traffic_db.campaign_merge 中的字段与语句的 INSERT 部分中的字段列表完全一致，应该可以工作。

有关 INSERT...SELECT 的文档可以在 here 中找到