How to UPDATE from JOIN with GROUP BY in MySQL

group byjoin;MySQLqueryupdate

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

Best Answer

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

Related Solutions

Postgresql – Retrieving data from inner join using LIMIT and OFFSET

Use a subquery (as displayed) or CTE for that purpose:

SELECT *
FROM  (
   SELECT qid, gid
   FROM   table1
   ORDER  BY date DESC
   LIMIT  10
   OFFSET ?
   ) q
JOIN   table2 a USING (qid, gid)

USING (qid, gid) is just a shortcut for ON q.qid = a.qid AND q.gid = a.gid with the side effect that the two columns are only included once in the result.

How to UPDATE a Column by COUNT of FK from Another Table in MySQL

You can do an UPDATE JOIN of table1 against a subquery that aggregates id counts in table2

UPDATE
    table1 A INNER JOIN
    (SELECT id,COUNT(1) idcount
    FROM table2 GROUP BY id) B
    USING (id)
SET A.number = B.idcount;

This query will not get every id. Why? If there is an id in table1 that is missing in table2, that does not write a zero in the numbers column.

To cover for id values missing in table2, run this one:

UPDATE
    table1 A LEFT JOIN
    (SELECT id,COUNT(1) idcount
    FROM table2 GROUP BY id) B
    USING (id)
SET A.number = IFNULL(B.idcount,0);

Give it a Try !!!

Best Answer

Related Solutions

Postgresql – Retrieving data from inner join using LIMIT and OFFSET

How to UPDATE a Column by COUNT of FK from Another Table in MySQL

Related Question