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
When updating with a
JOIN
, instead of doing aJOIN
, 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 yourWHERE
condition, likeWHERE t1.someCol=t2.someOtherCol
So instead of a join, you could write your query like this