T-SQL Problem converting a Cursor into a SET based operation

Basically I have this cursor that was not written by me but is taking some time to process and I was wanting to try and improve it by getting rid of the cursor all together.

Here is the code:

DECLARE @class_id int, @title_code varchar(30)

DECLARE title_class CURSOR FOR
SELECT DISTINCT title_code FROM tmp_business_class_titles (NOLOCK)

OPEN title_class

    FETCH title_class INTO @title_code

    WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT TOP 1 @class_id = bc1.categoryid
            FROM tmp_business_class_titles bct, 
            dbo.Categories bc1 (nolock) 
            join dbo.Categories bc2 (nolock) on bc2.categoryid = bc1.highercategoryid
            join dbo.Categories bc3 (nolock) on bc3.categoryid = bc2.highercategoryid 
            WHERE bc1.categoryid = bct.class_id
                AND title_code = @title_code
            ORDER BY Default_Flag DESC

            UPDATE products 
            SET subcategoryid = @cl开发者_运维问答ass_id 
            WHERE ccode = @title_code 
                AND spdisplaytype = 'Table'

            UPDATE products
            SET subcategoryid = @class_id
            WHERE highercatalogid IN (
                SELECT catalogid FROM products (nolock)
                WHERE ccode = @title_code AND spdisplaytype = 'Table')

            FETCH title_class INTO @title_code
        END

CLOSE title_class

DEALLOCATE title_class

The table tmp_business_class_titles looks like this:

class_id,title_code,Default_flag

7,101WGA,0

7,10315,0

29,8600,0

The default flag can always be 0 but if it is 1 then the logic should automatically pick the default class_id for that title_id.

So the current logic loops through the above table in a cursor and then selects the top 1 class id for each title, ordered by the the default flag (so the class_id with a default_flag of 1 should always be returned first.) and applies the default class_id to the products table.

This code takes around 1:20 to run and I am trying to convert this into one or 2 update statements but I have exhausted my brain in doing so.

Any TSQL Guru's have any ideas if this is possible or should I re-evaluate the entire logic on how the default flag works?

cheers for any help.

I don't have quite enough information to work with, so the following query is likely to fail. I particularly need more information on the products table to make this work, but assuming that you have SQL Server 2005 or higher, this might be enough to get you started in the right direction. It utilizes common table expressions along with the RANK function. I highly recommend learning about them, and in all likelihood, it will greatly improve the efficiency of the query.

;WITH cteTitle As (
    SELECT 
        sequence = RANK() OVER (PARTITION BY bct.title_code ORDER BY Default_Flag desc)
        ,bct.title_code
        ,bc1.categoryid
    FROM
        tmp_business_class_titles bct
        join Categories bc1 ON bc1.categoryid = bct.class_id
        join Categories bc2 ON bc2.categoryid = bc1.highercategoryid
        join Categories bc3 ON bc3.categoryid = bc2.highercategoryid
 )                            
 UPDATE 
     prod
 SET
     subcategoryid = ISNULL(t.categoryid,t2.categoryid)
 FROM
     products prod
     LEFT join products subprod ON subprod.catalogid = prod.highercatalogid
     LEFT join cteTitle t ON prod.ccode = t.title_code AND t.sequence = 1 AND prod.spdisplaytype = 'Table'
     LEFT join cteTitle t2 ON subprod.ccode = t2.title_code And t2.sequence = 1 AND subprod.spdisplaytype = 'Table'
 WHERE
     t2.categoryid IS NOT NULL