snippetsqlMinor

How to UPDATE from JOIN with GROUP BY

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

updategroupwithjoinhowfrom

Problem

In 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>0

How can I turn this query to UPDATE as

UPDATE b.number = counted

Solution

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
SET b1.number = b2.counted
WHERE b1.id = b2.id

Code Snippets

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
SET b1.number = b2.counted
WHERE b1.id = b2.id

Context

StackExchange Database Administrators Q#218762, answer score: 7

Revisions (0)

No revisions yet.