Post reply

Updating a temp table with a count of occurrances in a different table?

  • February 7, 2009 at 10:35 am

    #127443

    I've been racking my brain on this one for a day now, and I still can't figure it out... First, the premise - it's a tagging system for my site's blog. In order to generate the seemingly popular "tag cloud", I create a temporary table #Tags, which contains [Tag] for the value of the tag, and [Count]. [Tag] is populated from a SELECT DISTINCT from [www_Tags], the regular table that stores all the tags for every post. What I need to do now is go back and update #Tags.[Count] with the count of how many times that tag appears in [www_Tags]... and it's driving me nuts. I know it's possible because we do something similar at work, but I'm not at work right now...

    UPDATE #Tags

    SET [Count] = (SELECT COUNT([Tag]) FROM [www_Tags] wT WHERE wT.[Tag] = [Tag])

    That's what I have in there now... fails rather miserably, and I'm sure I know why... every attempt since then won't even compile...

    Any ideas?

    __________________________________
    I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...

    And now I'm lost...

  • February 7, 2009 at 10:42 am

    #940012

    You need to use the FROM clause of the update

    UPDATE #Tags

    SET [Count] = TotalCounts

    FROM #Tags

    INNER JOIN (

    SELECT COUNT([Tag]) as TotalCounts, Tag

    FROM [www_Tags] wT

    GROUP BY Tag) sub on #Tags.Tag = sub.Tags

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 7, 2009 at 10:47 am

    #940015

    The reason why is because the correlated subquery that makes the count gets executed for every row in your temp table. You need to do a little "pre-aggregation" and then join to it... like this (untested)...

    UPDATE #Tags

    SET [Count] = c.Cnt

    FROM #Tags t

    INNER JOIN

    (SELECT Tag, COUNT(*) AS Cnt

    FROM WWW_Tags

    GROUP BY Tag) c

    ON t.Tag = c.Tag

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 7, 2009 at 11:03 am

    #940021

    Works perfectly. Thanks!

    __________________________________
    I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...

    And now I'm lost...

  • February 7, 2009 at 11:18 am

    #940025

    Dave (2/7/2009)

    Works perfectly. Thanks!

    You bet... thank you for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply