SQL Server Performance

Trying not to use a Cursor

Discussion in 'SQL Server 2005 General Developer Questions' started by L0st_Pr0phet, Sep 6, 2007.

  1. L0st_Pr0phet Member

    Hi, I was hoping someone out there could give me a hand.
    I am creating a stored procedure that is fired by a stored procedure using a cursor. I dont really want to use another cursor so was wondering if there was anyway of doing the following without one.
    Scenerio:
    I have a table that has a composite key of Office ID and WBID, and it has a identity column called temp_id. Now this table gets populate by a import process. The problem is it is possible for more than one office to order the same WBID. Now I want to be able to import all orders fine but then programatically in T-SQL go through and delete any duplicates of the WBID in the table leaving the first occurance. Any Ideas? Example below
    Table:
    WBID Office Order Dup Temp_ID
    14 A8 yes 1 1 * duplicate WBID 14
    15 A9 yes null 2
    14 A10 yes 1 3 * duplicate WBID 14
    20 A10 yes null 4
    21 A23 yes 1 5 * duplicate WBID 21
    21 A24 yes 1 6 * duplicate WBID 21
    I can mark the duplicates fine by using a group by WBID Having count(WBID)>1 but cant think how to delete the records without using a cursor.
    cheers
    stew
  2. Adriaan New Member

    Deleting duplicates is a frequent request on these forums ...
    This will delete duplicates based purely on WBID, ignoring any other column, and leaving the row with the lowest Temp_ID:
    DELETE FROM tableWB t1
    WHERE EXISTS
    (SELECT * FROM tableWB t2
    WHERE t2.WBID = t1.WBID
    AND t2.Temp_ID < t1.Temp_ID)
  3. L0st_Pr0phet Member

    Excellent thats just what I was after
    thanks

Share This Page