How to add the number of rows of the first table to another with the condition









up vote
0
down vote

favorite












There are two tables:



####comments#### ####news######
#cid#news_id# #id##comm_num#
#1##1# #1###2#
#2##1# #2###1#
#3##2# #3###3#


I try to count and put the number of comments from the table 'comments' into the table 'news', but I get the wrong result. Why?



UPDATE news JOIN comments ON news.id = comments.news_id SET 
news.comm_num = ( SELECT COUNT( * )
FROM comments WHERE comments.news_id > 123)
WHERE news.id > 123


'comments.news_id' = 'id' of commented news from table 'news'



I wrote a working solution for particular cases, but I can’t figure out how to make a request with a condition larger than.



UPDATE news a 
SET comm_num = (SELECT COUNT(*)
FROM comments c
WHERE c.news_id = 123)
WHERE a.id = 123









share|improve this question



























    up vote
    0
    down vote

    favorite












    There are two tables:



    ####comments#### ####news######
    #cid#news_id# #id##comm_num#
    #1##1# #1###2#
    #2##1# #2###1#
    #3##2# #3###3#


    I try to count and put the number of comments from the table 'comments' into the table 'news', but I get the wrong result. Why?



    UPDATE news JOIN comments ON news.id = comments.news_id SET 
    news.comm_num = ( SELECT COUNT( * )
    FROM comments WHERE comments.news_id > 123)
    WHERE news.id > 123


    'comments.news_id' = 'id' of commented news from table 'news'



    I wrote a working solution for particular cases, but I can’t figure out how to make a request with a condition larger than.



    UPDATE news a 
    SET comm_num = (SELECT COUNT(*)
    FROM comments c
    WHERE c.news_id = 123)
    WHERE a.id = 123









    share|improve this question

























      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      There are two tables:



      ####comments#### ####news######
      #cid#news_id# #id##comm_num#
      #1##1# #1###2#
      #2##1# #2###1#
      #3##2# #3###3#


      I try to count and put the number of comments from the table 'comments' into the table 'news', but I get the wrong result. Why?



      UPDATE news JOIN comments ON news.id = comments.news_id SET 
      news.comm_num = ( SELECT COUNT( * )
      FROM comments WHERE comments.news_id > 123)
      WHERE news.id > 123


      'comments.news_id' = 'id' of commented news from table 'news'



      I wrote a working solution for particular cases, but I can’t figure out how to make a request with a condition larger than.



      UPDATE news a 
      SET comm_num = (SELECT COUNT(*)
      FROM comments c
      WHERE c.news_id = 123)
      WHERE a.id = 123









      share|improve this question















      There are two tables:



      ####comments#### ####news######
      #cid#news_id# #id##comm_num#
      #1##1# #1###2#
      #2##1# #2###1#
      #3##2# #3###3#


      I try to count and put the number of comments from the table 'comments' into the table 'news', but I get the wrong result. Why?



      UPDATE news JOIN comments ON news.id = comments.news_id SET 
      news.comm_num = ( SELECT COUNT( * )
      FROM comments WHERE comments.news_id > 123)
      WHERE news.id > 123


      'comments.news_id' = 'id' of commented news from table 'news'



      I wrote a working solution for particular cases, but I can’t figure out how to make a request with a condition larger than.



      UPDATE news a 
      SET comm_num = (SELECT COUNT(*)
      FROM comments c
      WHERE c.news_id = 123)
      WHERE a.id = 123






      mysql sql






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 10 at 14:53









      Gordon Linoff

      742k32285390




      742k32285390










      asked Nov 10 at 14:45









      noobsaibot

      257




      257






















          2 Answers
          2






          active

          oldest

          votes

















          up vote
          1
          down vote



          accepted










          Simply use a correlated subquery:



          UPDATE news n 
          SET comm_num = (SELECT COUNT(*)
          FROM comments c
          WHERE c.news_id = n.id
          ) ;


          I am not sure what the condition WHERE news.id > 123 is for.






          share|improve this answer



























            up vote
            0
            down vote













            You can get the count of total comments on a news using a Derived Table. Join this back to the news table on the news_id and update the values accordingly.



            We use LEFT JOIN to handle the case where there is no comments on a news. And, Coalesce() function is used to change null to 0 (in case of no comments).



            UPDATE news a 
            LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
            FROM comments
            GROUP BY news_id) b
            ON b.news_id = a.id
            SET a.comm_num = COALESCE(b.comm_num,0)


            If you want to update only those news where id is more than 123; you can add the conditions as follows:



            UPDATE news a 
            LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
            FROM comments
            WHERE news_id > 123 -- add condition to Derived Table
            GROUP BY news_id) b
            ON b.news_id = a.id
            WHERE a.id > 123 -- add condition here also to avoid updating id <= 123
            SET a.comm_num = COALESCE(b.comm_num,0)





            share|improve this answer






















              Your Answer






              StackExchange.ifUsing("editor", function ()
              StackExchange.using("externalEditor", function ()
              StackExchange.using("snippets", function ()
              StackExchange.snippets.init();
              );
              );
              , "code-snippets");

              StackExchange.ready(function()
              var channelOptions =
              tags: "".split(" "),
              id: "1"
              ;
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function()
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled)
              StackExchange.using("snippets", function()
              createEditor();
              );

              else
              createEditor();

              );

              function createEditor()
              StackExchange.prepareEditor(
              heartbeatType: 'answer',
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader:
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              ,
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              );



              );













               

              draft saved


              draft discarded


















              StackExchange.ready(
              function ()
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53240063%2fhow-to-add-the-number-of-rows-of-the-first-table-to-another-with-the-condition%23new-answer', 'question_page');

              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes








              up vote
              1
              down vote



              accepted










              Simply use a correlated subquery:



              UPDATE news n 
              SET comm_num = (SELECT COUNT(*)
              FROM comments c
              WHERE c.news_id = n.id
              ) ;


              I am not sure what the condition WHERE news.id > 123 is for.






              share|improve this answer
























                up vote
                1
                down vote



                accepted










                Simply use a correlated subquery:



                UPDATE news n 
                SET comm_num = (SELECT COUNT(*)
                FROM comments c
                WHERE c.news_id = n.id
                ) ;


                I am not sure what the condition WHERE news.id > 123 is for.






                share|improve this answer






















                  up vote
                  1
                  down vote



                  accepted







                  up vote
                  1
                  down vote



                  accepted






                  Simply use a correlated subquery:



                  UPDATE news n 
                  SET comm_num = (SELECT COUNT(*)
                  FROM comments c
                  WHERE c.news_id = n.id
                  ) ;


                  I am not sure what the condition WHERE news.id > 123 is for.






                  share|improve this answer












                  Simply use a correlated subquery:



                  UPDATE news n 
                  SET comm_num = (SELECT COUNT(*)
                  FROM comments c
                  WHERE c.news_id = n.id
                  ) ;


                  I am not sure what the condition WHERE news.id > 123 is for.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 10 at 14:51









                  Gordon Linoff

                  742k32285390




                  742k32285390






















                      up vote
                      0
                      down vote













                      You can get the count of total comments on a news using a Derived Table. Join this back to the news table on the news_id and update the values accordingly.



                      We use LEFT JOIN to handle the case where there is no comments on a news. And, Coalesce() function is used to change null to 0 (in case of no comments).



                      UPDATE news a 
                      LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
                      FROM comments
                      GROUP BY news_id) b
                      ON b.news_id = a.id
                      SET a.comm_num = COALESCE(b.comm_num,0)


                      If you want to update only those news where id is more than 123; you can add the conditions as follows:



                      UPDATE news a 
                      LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
                      FROM comments
                      WHERE news_id > 123 -- add condition to Derived Table
                      GROUP BY news_id) b
                      ON b.news_id = a.id
                      WHERE a.id > 123 -- add condition here also to avoid updating id <= 123
                      SET a.comm_num = COALESCE(b.comm_num,0)





                      share|improve this answer


























                        up vote
                        0
                        down vote













                        You can get the count of total comments on a news using a Derived Table. Join this back to the news table on the news_id and update the values accordingly.



                        We use LEFT JOIN to handle the case where there is no comments on a news. And, Coalesce() function is used to change null to 0 (in case of no comments).



                        UPDATE news a 
                        LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
                        FROM comments
                        GROUP BY news_id) b
                        ON b.news_id = a.id
                        SET a.comm_num = COALESCE(b.comm_num,0)


                        If you want to update only those news where id is more than 123; you can add the conditions as follows:



                        UPDATE news a 
                        LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
                        FROM comments
                        WHERE news_id > 123 -- add condition to Derived Table
                        GROUP BY news_id) b
                        ON b.news_id = a.id
                        WHERE a.id > 123 -- add condition here also to avoid updating id <= 123
                        SET a.comm_num = COALESCE(b.comm_num,0)





                        share|improve this answer
























                          up vote
                          0
                          down vote










                          up vote
                          0
                          down vote









                          You can get the count of total comments on a news using a Derived Table. Join this back to the news table on the news_id and update the values accordingly.



                          We use LEFT JOIN to handle the case where there is no comments on a news. And, Coalesce() function is used to change null to 0 (in case of no comments).



                          UPDATE news a 
                          LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
                          FROM comments
                          GROUP BY news_id) b
                          ON b.news_id = a.id
                          SET a.comm_num = COALESCE(b.comm_num,0)


                          If you want to update only those news where id is more than 123; you can add the conditions as follows:



                          UPDATE news a 
                          LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
                          FROM comments
                          WHERE news_id > 123 -- add condition to Derived Table
                          GROUP BY news_id) b
                          ON b.news_id = a.id
                          WHERE a.id > 123 -- add condition here also to avoid updating id <= 123
                          SET a.comm_num = COALESCE(b.comm_num,0)





                          share|improve this answer














                          You can get the count of total comments on a news using a Derived Table. Join this back to the news table on the news_id and update the values accordingly.



                          We use LEFT JOIN to handle the case where there is no comments on a news. And, Coalesce() function is used to change null to 0 (in case of no comments).



                          UPDATE news a 
                          LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
                          FROM comments
                          GROUP BY news_id) b
                          ON b.news_id = a.id
                          SET a.comm_num = COALESCE(b.comm_num,0)


                          If you want to update only those news where id is more than 123; you can add the conditions as follows:



                          UPDATE news a 
                          LEFT JOIN (SELECT news_id, COUNT(*) AS comm_num
                          FROM comments
                          WHERE news_id > 123 -- add condition to Derived Table
                          GROUP BY news_id) b
                          ON b.news_id = a.id
                          WHERE a.id > 123 -- add condition here also to avoid updating id <= 123
                          SET a.comm_num = COALESCE(b.comm_num,0)






                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Nov 10 at 14:57

























                          answered Nov 10 at 14:51









                          Madhur Bhaiya

                          15.3k52136




                          15.3k52136



























                               

                              draft saved


                              draft discarded















































                               


                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function ()
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53240063%2fhow-to-add-the-number-of-rows-of-the-first-table-to-another-with-the-condition%23new-answer', 'question_page');

                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Top Tejano songwriter Luis Silva dead of heart attack at 64

                              政党

                              天津地下鉄3号線