Menu
I'm trying to set the value in one table to the sum of the values in another table. Something along these lines:
UPDATE table1 SET field1 = SUM(table2.field2) FROM table1 INNER JOIN table2 ON table1.field3 = table2.field3 GROUP BY table1.field3 Of course, as this stands, it won't work - SET doesn't support SUM and it doesn't support GROUP BY.
I should know this, but my mind's drawing a blank. What am I doing wrong?
971
An aggregate may not appear in the set list of an UPDATE statement. But SQL doesn't always agree that it should be simple. Let's setup a contrived example using a data set of Airport Gate information from San Francisco Airport. I have 1 table which has all of the times a Gate has been used at the airport.
You can't issue an UPDATE statement using a group by. The point of using GROUP BY is to change the way that the result set is displayed to the user. When you have a GROUP BY statement you utilize the HAVING clause to filer the aggregated result set.
An aggregate function in SQL performs a calculation on multiple values and returns a single value. SQL provides many aggregate functions that include avg, count, sum, min, max, etc. An aggregate function ignores NULL values when it performs the calculation, except for the count function.
Except for the COUNT() function, SQL aggregate functions ignore null. You can use aggregate functions as expressions only in the following: The select list of a SELECT statement, either a subquery or an outer query.
UPDATE t1 SET t1.field1 = t2.field2Sum FROM table1 t1 INNER JOIN (select field3, sum(field2) as field2Sum from table2 group by field3) as t2 on t2.field3 = t1.field3 Use:
UPDATE table1 SET field1 = (SELECT SUM(t2.field2) FROM TABLE2 t2 WHERE t2.field3 = field2) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
