Update column with itself plus value of column in another table -- accumulating?

  • I included the cursor example just to show you what a simple one looked like, not so you could use it for the basis of all future work. They aren't a "powerful construct", they're (usually) a sign that you've given up trying to find a better set-based solution.

    The cursor example does one update per row and is the least efficient/scalable. The WHILE loop that does one update per party (at most) is better, and the single UPDATE with the XML subquery is the best of the three. Considering that you populated the oc_daddr table with a GROUP BY query and are coming back to it with an UPDATE to fill in parties, the most efficient of the four would be to load the table in one pass from the SELECT. But that can still be improved on.

    The XML subquery is a form of correlated subquery, so the oc_addr table will be scanned again for every unique address. Something that only requires one pass would be more efficient. If the list of possible parties is known in advance, this would work:

    TRUNCATE TABLE dbo.oc_daddr;

    INSERT INTO dbo.oc_daddr

    SELECT Addr, COUNT(*) AS members,

    parties = ISNULL(MAX(CASE party WHEN 'AI' THEN '|AI' ELSE NULL END), '')

    + ISNULL(MAX(CASE party WHEN 'DEM' THEN '|DEM' ELSE NULL END), '')

    + ISNULL(MAX(CASE party WHEN 'DS' THEN '|DS' ELSE NULL END), '')

    + ISNULL(MAX(CASE party WHEN 'REP' THEN '|REP' ELSE NULL END), '')

    FROM oc_addr

    GROUP BY Addr

    There is probably someone out there who can come up with something better still. And they're probably lurking on this website ready to answer your questions for free (as long as you ask nicely). If you read Books Online at least to the point where you know what @ and @@ are for (nothing about passing by reference), you'll get even better answers.

  • I'll have to keep in mind that holding onto my previous row-wise approach is a crutch, and one likely to handicap my ability to exploit SQL Server. I'll look at and link books online today. Thanks!

    As an aside, depending on the county, there are between 60 and 206 "ACTIVE" party designations including such "mainstream" groups as:

    Rock and Roll Party

    Dudes International Party

    More Money for Women Party

    Block Party

    Birthday Party

    Stargazer Party

    Formenorian Monarchy Party (WTF?)

    Superhappy Evolution & Neuroscience (with ties to the pharmaceutical lobby ya think?)

    We Like Women and Science (pretty sure this one started in the dorms at CalTech), and

    Hespanic (yes, with an 'E'!)

    etc.

    I don't know if all this is a hopeful sign, or an indication of our impending doom... Just thought you'd get a kick out of it.

  • What, no Jedi party?

Viewing 3 posts - 16 through 17 (of 17 total)

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