SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update with select count

    I have a situation where I want to get a count of grouped records from one table and update a field with the count in another table.

    the select statement works fine as

    select count(t1.f1) as cnt, t1.f2, t1.f3 from reviews group by t1.f2, t1.f3

    works great.

    Now I want to take cnt and update a field t2.f1 where t1.f2 = t2.f2 and t1.f3 = t2.f3

    Every syntax I've tried I doesn't work and I can't seem to find anything in the manual. Can someone point me in the right direction. Thanks

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure exactly what you want, but one way to do it could be to store cnt in a user defined variable and then use it to update the field you want to update

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    kalon, the problem is, there isn't just one count

    you would need a separate user variable for every combination of t1.f2 and t1.f3 -- check the GROUP BY clause
    Code:
    UPDATE t2
    INNER
      JOIN ( SELECT count(f1) as cnt
                  , f2
                  , f3 
              from reviews 
             group 
                by f2
                 , f3 ) AS t1
        ON t1.f2 = t2.f2
       AND t1.f3 = t2.f3
       SET t2.f1 = t1.cnt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    kalon, the problem is, there isn't just one count
    That's why I said I wasn't sure what the OP wanted because the op said
    .......I want to get a count of grouped records......
    which clearly means only 1 count.

    For multiple counts it should have been

    .......I want to get counts of grouped records......
    but I'm not an English teacher so my understanding of English grammar could be wrong.


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
  •