INNER JOIN SELECT in MySQL

Question

I have an SQL statement that works in SQL Server but fails in MySQL... shouldn't this work in MySQL?

UPDATE T2
    SET TotalAmount = T1.SumAmount
    FROM ccs_multiples as T2
    INNER JOIN (SELECT SUM(Amount) as SumAmount, SerialNumber
                FROM ccs_multiples_items
                GROUP BY SerialNumber) as T1
    ON T2.SerialNumber = T1.SerialNumber

Error:

#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 ccs_multiples as T2 INNER JOIN (SELECT SUM(Amount) as SumAmount, Seria at line 3

Answer 1

When updating with a JOIN, instead of doing a JOIN, you can specify multiple tables in the update, ie.

UPDATE table1 t1, table2 t2

and then specify what would typically be your JOIN condition, instead as part of your WHERE condition, like

WHERE t1.someCol=t2.someOtherCol

So instead of a join, you could write your query like this

UPDATE 
  ccs_multiples T2,
  (SELECT 
    SUM(Amount) AS SumAmount,
    SerialNumber 
  FROM
    ccs_multiples_items 
  GROUP BY SerialNumber) T1 
SET
  T2.TotalAmount = T1.SumAmount 
WHERE T2.SerialNumber = T1.SerialNumber 

Answer 2

The syntax for a multi-table update in MySQL is different than SQL Server.

You can use syntax like this:

  UPDATE ccs_multiples T2
    JOIN ( SELECT SUM(i.Amount) AS SumAmount
                , i.SerialNumber
             FROM ccs_multiples_items i
            GROUP BY i.SerialNumber
         ) T1
      ON T2.SerialNumber = T1.SerialNumber
     SET T2.TotalAmount = T1.SumAmount

NOTES:

In MySQL, the INNER keyword is optional; omitting it has no effect on the statement.

But, what would you want to do with rows in ccs_multiples that don't have any corresponding rows in ccs_multiples_items. To set the TotalAmount column to zero for those rows, you could use an outer join:

  UPDATE ccs_multiples T2
    LEFT
    JOIN ( SELECT SUM(i.Amount) AS SumAmount
                , i.SerialNumber
             FROM ccs_multiples_items i
            GROUP BY i.SerialNumber
         ) T1
      ON T2.SerialNumber = T1.SerialNumber
     SET T2.TotalAmount = IFNULL(T1.SumAmount,0)

---

One thing to note about the MySQL syntax, we can (usually) convert a SELECT statement into an UPDATE statement. I usually start with a SELECT to test the predicates and the expressions. For example:

SELECT T2.SerialNumber
     , T2.TotalAmount          AS existing_TotalAmount
     , T1.SumAmount
     , IFNULL(T1.SumAmount,0)  AS new_TotalAmount
  FROM ccs_multiples T2
    LEFT
    JOIN ( SELECT SUM(i.Amount) AS SumAmount
                , i.SerialNumber
             FROM ccs_multiples_items i
            GROUP BY i.SerialNumber
         ) T1
      ON T2.SerialNumber = T1.SerialNumber

We can test that SELECT, and verify that the expressions are working correctly. To convert that into an UPDATE statement, we just remove the SELECT ... FROM from the beginning of the statement, and replace it with UPDATE and add a SET clause before the WHERE clause.