How to UPDATE with join sql server with GROUP BY?
Here is a SELECT query of SELECT b.id, MIN(IFNULL(a.views,0)) AS counted FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id HAVING counted> How can I turn this query to UPDATE as UPDATE b.number = counted How to update with join sql server? Thanks in advance!
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. UPDATE table2 AS b1, ( SELECT b.id, MIN(IFNULL(a.views, 0)) AS counted
- FROM table1 a
- JOIN table2 b ON a.id = b.id
- GROUP BY id
- HAVING counted > 0 ) AS b2 n SET b1.number = b2.counted WHERE b1.id = b2.id