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
  • 1
    Update query with left join and group by: stackoverflow.com/questions/3022405/… – CR241 Sep 27 '18 at 19:56
  • 1
    counted is the result of an aggregation, so how do you determine which row in table2/b to update? is it possible that you could update multiple rows with the same counted value? – markp Sep 27 '18 at 20:21
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.