从另一张表中插入数据到 MySQL

Question

INSERT INTO campaign_ledger (`campaign_id`, `description`, amount, balance, timestamp)
VALUES (SELECT id as campaign_id, 'Ported from campaigns' as description, budget_remaining as amount, budget_remaining as balance, NOW() as timestamp FROM campaigns)

这是我的语法，但我收到了一个错误，其中提到：

"您的SQL语法存在错误；请检查您的MySQL服务器版本对应的手册以获取正确的语法，在第2行附近使用的语法不正确。"

我做错了什么？

- Shamoon

Answer 1

由于您正在从表中进行选择，因此您需要使用一个INSERT INTO SELECT FROM查询：

INSERT INTO campaign_ledger 
(
    `campaign_id`
    , `description`
    , amount
    , balance
    , timestamp
)
SELECT 
    id as campaign_id
    , 'Ported from campaigns' as description
    , budget_remaining as amount 
    , budget_remaining as balance
    , NOW() as timestamp 
FROM campaigns

只有在使用特定值而不是从表中选择时才使用INSERT INTO VALUES。如果您想要使用INSERT INTO VALUES，则查询应该像这样：

INSERT INTO campaign_ledger 
(
    `campaign_id`
    , `description`
    , amount
    , balance
    , timestamp
)
VALUES
(
    1
    , 'test'
    , 100.00
    , 1000.00
    , NOW()
)

- Taryn

Answer 2

INSERT INTO campaign_ledger (`campaign_id`, `description`, amount, balance, timestamp)
SELECT id as campaign_id, 'Ported from campaigns' as description,
budget_remaining as amount,budget_remaining as balance,
NOW() as timestamp FROM campaigns;

- RolandoMySQLDBA

Answer 3

查询中的VALUES部分不是必需的。例如：

 INSERT INTO campaign_ledger (`campaign_id`, `description`, amount, balance, timestamp)
 SELECT id as campaign_id, 'Ported from campaigns' as description, 
        budget_remaining as amount, budget_remaining as balance, 
        NOW() as timestamp 
 FROM campaigns;

- Ryan