Re: Delete higher price record, if there is one.

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 04/08/04 

Date: Thu, 08 Apr 2004 22:51:53 +0200

On Thu, 8 Apr 2004 13:41:07 -0700, newbie k wrote:

>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,

DELETE FROM dbo.vwDupSKU
WHERE EXISTS
  (SELECT *
   FROM dbo.vwDupSKU vwDupSKU_1
   WHERE dbo.vwDupSKU.STORENO = vwDupSKU_1.STORENO
   AND dbo.vwDupSKU.ITEMNUMBER = vwDupSKU_1.ITEMNUMBER
   AND dbo.vwDupSKU.RETAILPRICE > vwDupSKU_1.RETAILPRICE)

Check the results and rollback or commit as required.

(Note - check very carefully! Somehow, I always seem to be able to
delete the lower priced rows instead of the higher priced on queries
like this - obviously a blind spot in my brain <g>)

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)