从另一个表插入MySQL [英] Insert INTO MySQL FROM another table

问题描述

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)

这是我的语法，但是我收到一条错误消息:

That's my syntax, but I get an error saying:

您的SQL语法有错误；检查手册 对应于您的MySQL服务器版本以使用正确的语法 在选择ID为campaign_id"附近，从广告系列移植"为 描述，第2行的budget_remaini'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id as campaign_id, 'Ported from campaigns' as description, budget_remaini' at line 2

我在做什么错了?

推荐答案

由于要从表中进行选择，因此需要使用INSERT INTO SELECT FROM查询:

Since you are selecting from a table then you will want to use an INSERT INTO SELECT FROM query:

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，则查询将如下所示:

Only use INSERT INTO VALUES when you are using specific values and not selecting from a table. If you wanted to use INSERT INTO VALUES then your query would be like this:

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

这篇关于从另一个表插入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！