mysql - 与 MySQL 的 JOIN 语句

Question

我正在努力使用 MySQL 连接两个表。

第一个表是客户的子集，称为“merge1”，包含以下记录:

    customer_id   First Name      Total Spent    Total Orders
    ==========================================================
    1              Mike              0.00          0.00
    2              Allan             0.00          0.00
    3              Craig             0.00          0.00
    4              Michael           0.00          0.00

表2有销售数据，如下所示，名为“merge2”，包含以下记录:

    customer_id   Total Spent   Total Orders
    =========================================
    1                65.00            1
    2                64.00            1
    3               109.00            1
    4                65.00            1

我尝试通过运行以下 SQL 来更新第一个表(合并 1)中的“总支出”和“总订单”:

    INSERT INTO `merge1` (`customer_id`, `Total Spent`, `Total Orders`)

    SELECT 
        merge2.`customer_id`,
        merge2.`Total Spent`,
        merge2.`Total Orders`

    FROM  
      merge2

    JOIN
        merge1 ON merge2.customer_id = merge1.customer_id;

查询没有出错，但表没有更新。

有人能指出我正确的方向吗，我非常感谢您的帮助!？

回答

非常感谢下面的评论!我应该使用“更新”命令。这是我使用的语法，如果它对其他人有用的话。

    UPDATE merge1 T1 JOIN merge2 T2
    SET    T1.`Total Spent` = T2.`Total Spent`, T1.`Total Orders` = T2.`Total Orders`
    WHERE  T1.customer_id = T2.customer_id;

Answer 1

嘿，如果您需要插入，请使用:

INSERT INTO `merge1`(`customer_id`, `Total Spent`, `Total Orders`) SELECT 
        merge2.`customer_id` AS customer_id,merge2.`Total Spent` AS Total Spent,merge2.`Total Orders` AS Total Orders FROM merge2

或者如果您需要更新，请使用:

UPDATE `merge1` JOIN `merge2` ON merge1.customer_id = merge2.customer_id
SET merge1.`Total Spent` = merge2.`Total Spent`,merge1.`Total Orders` = merge2.`Total Orders`

让我知道发生了什么:)