Updating table from another select statement

Question

I'm trying to update my columns by selecting data from another table. I've come up with this SQL where it will select data from two tables and update it to my main table. SQL as below.

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

However, I get this error

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

Where it went wrong with the statement above?

Answer 1

MySQL does not support this join syntax. You should use an explicit inner join between the activity table and subquery:

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;