Delete higher price record, if there is one.

From: newbie k (anonymous_at_discussions.microsoft.com)
Date: 04/08/04

  • Next message: Hugo Kornelis: "Re: Delete higher price record, if there is one." 
    Date: Thu, 8 Apr 2004 13:41:07 -0700

    I have the below store procedure to delete out records that have a higher price, if there is a higher price record found in the view that I created. If not don't delete. Any ideas how to do this without using a cursor, it takes way too long. thanks,

    DECLARE @MyCursor CURSOR
    DECLARE @ITEMNUMBER as char(10)
    DECLARE @RETAILPRICE as char(15)
    SET @MyCursor = CURSOR FAST_FORWARD
    FOR
    SELECT dbo.vwDupSKU.ITEMNUMBER, vwDupSKU.RETAILPRICE
            FROM dbo.vwDupSKU INNER JOIN
                      dbo.vwDupSKU vwDupSKU_1 ON dbo.vwDupSKU.STORENO = vwDupSKU_1.STORENO AND
            dbo.vwDupSKU.ITEMNUMBER = vwDupSKU_1.ITEMNUMBER And dbo.vwDupSKU.RETAILPRICE > vwDupSKU_1.RETAILPRICE

    OPEN @MyCursor
    FETCH NEXT FROM @MyCursor
    INTO @ITEMNUMBER, @RETAILPRICE

    View:
    CREATE VIEW dbo.vwDupSKU
    AS
    SELECT TOP 100 PERCENT STORENO, ITEMNUMBER, PROMOREG, RETAILPRICE, VENDOR, SKUUNITS, ITEMDESC, SKU, POS_DESCRIPTION, RETAIL_SKU,
                          EFFECTIVE_TIMESTAMP
    FROM dbo.tblTagPrint01
    WHERE (STORENO IN
                              (SELECT [STORENO]
                                FROM dbo.tblTagPrint01 AS Tmp
                                GROUP BY [STORENO], [ITEMNUMBER]
                                HAVING COUNT(*) > 1 AND [ITEMNUMBER] = dbo.tblTagPrint01.[ITEMNUMBER]))
    ORDER BY STORENO, ITEMNUMBER

       WHILE @@FETCH_STATUS = 0
          BEGIN
             Delete tblTagPrint01 where ITEMNUMBER = @ITEMNUMBER and RETAILPRICE = @RETAILPRICE
        FETCH NEXT FROM @MyCursor INTO @ITEMNUMBER, @RETAILPRICE
       END
    CLOSE @MyCursor
    DEALLOCATE @MyCursor
    GO

  • Next message: Hugo Kornelis: "Re: Delete higher price record, if there is one."