Professional Web Applications Themes

UPDATE with GROUP BY - MySQL

hi, consider tables 1. blogs (id, num_article) 2. articles (id, blog_id) is it possible to perform the query in the following statement? update blogs, articles set blogs.num_article = count(*) where blogs.id = articles.blog_id group by articles.blog_id thanks....

  1. #1

    Default UPDATE with GROUP BY

    hi,


    consider tables

    1. blogs (id, num_article)
    2. articles (id, blog_id)

    is it possible to perform the query in the following statement?

    update blogs, articles set blogs.num_article = count(*) where blogs.id
    = articles.blog_id group by articles.blog_id

    thanks.

    howachen@gmail.com Guest

  2. #2

    Default Re: UPDATE with GROUP BY


    com wrote: 

    This'll do it:

    UPDATE blogs,
    (
    SELECT blog_id, count( * ) AS ac
    FROM articles
    GROUP BY blog_id
    ) AS t1
    SET blogs.num_article = t1.ac WHERE blogs.id = t1.blog_id

    Captain Guest

  3. #3

    Default Re: UPDATE with GROUP BY

    com wrote: 

    No, there is no support for performing GROUP BY or aggregate
    calculations in an UPDATE statement. You can use a subquery if you have
    MySQL 4.1 or higher:

    update blogs set blogs.num_article = (select count(*) from articles
    where blogs.id = articles.blog_id);

    Note that it's unnecessary to GROUP BY the column in this case, since
    the subquery is calculated for each row of the blogs table, and so it is
    naturally for exactly one value of blog_id in each iteration.

    Another option is to do the update incrementally, instead of calculating
    the count():

    update blogs set num_articles = 0;

    update blogs, articles
    set blogs.num_article = blogs.num_article + 1
    where blogs.id = articles.blog_id

    This should get the same result.

    Regards,
    Bill K.
    Bill Guest

Similar Threads

  1. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  2. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. why is the "Adobe Acrobat 6.0.1 Update" message there at the top? -- the update doesn't work
    By john_cummin@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 6
    Last Post: August 5th, 01:51 PM
  5. 5.0.7 Update Pack 1 requires SCO Update license?
    By Scott McMillan in forum SCO
    Replies: 0
    Last Post: July 30th, 08:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139