mysql - 从另一个选择语句更新表

Question

我正在尝试通过从另一个表中选择数据来更新我的列。我想出了这个 SQL，它将从两个表中选择数据并将其更新到我的主表。 SQL如下。

UPDATE activity a 
SET a.amount = v.amount, 
    a.count = v.count 
FROM ( SELECT id,
                Sum(actual) amount,
                Count(id)  count
         FROM   amount_first
         WHERE  status = 1
                AND updated >= 1538323200
                AND updated <= 1541001599
         GROUP  BY id
         UNION
         SELECT id,
                Sum(0) amount,
                0      count
         FROM   amount_second
         WHERE  type = 3
                AND created >= 1538323200
                AND created <= 1541001599
         GROUP  BY id ) v
WHERE v.id = a.playerid

但是，我得到了这个错误

Error Code: 1064. 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 'FROM ( SELECT id, Sum(actual) amount, Count(id) count FROM i' at line 4

上面的说法哪里错了？

Answer 1

MySQL 不支持这种连接语法。您应该在 activity 表和子查询之间使用显式内部联接:

UPDATE activity a 
INNER JOIN
(
     SELECT id,
            Sum(actual) amount,
            Count(id)  count
     FROM   amount_first
     WHERE  status = 1
            AND updated >= 1538323200
            AND updated <= 1541001599
     GROUP  BY id
     UNION
     SELECT id,
            Sum(0) amount,
            0      count
     FROM   amount_second
     WHERE  type = 3
            AND created >= 1538323200
            AND created <= 1541001599
     GROUP  BY id
) v
    ON v.id = a.playerid
SET a.amount = v.amount, 
    a.count = v.count;