7

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
2
  • 1
    Update query with left join and group by: stackoverflow.com/questions/3022405/…
    – CR241
    Commented Sep 27, 2018 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-fuso
    Commented Sep 27, 2018 at 20:21

3 Answers 3

7
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
3
    UPDATE table2
    INNER JOIN (SELECT MIN(IFNULL(table1.views,0)) counted
    FROM table1
    GROUP BY table1.id
    HAVING counted>0
    ) x ON x.id = table2.id
    SET table2.number = x.counted
1
  • 1
    How's that different from the earlier, accepted answer?
    – mustaccio
    Commented Mar 19, 2020 at 13:31
-2
UPDATE table2 SET table2.number = x.counted  FROM table2
INNER JOIN (SELECT MIN(IFNULL(table1.views,0)) counted,id
FROM table1
GROUP BY table1.id
HAVING counted>0
) x ON x.id = table2.id

Try this also.

1
  • Hi Anwar, its best to explain, in your answer, whats different or why it works Commented Oct 8, 2023 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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