CODEWITHSUNDEEP
t-sqlsql-server-2008-r2cursor
Cat
Cat

Reputation: 103

Cursor updates all rows instead of one

I need to go over a view (vRidesForPricing) and compare each row with the content of another view (vRidesDone).

I'm searching for a match - if CompanyID, SourceId and DestinationId of the current row of RidesDone matches those fields in RidesForPricing, then I want to update RidesForPricing with the OfficePrice value of RidesDone (meaning if the exact same ride happened before I just want to take its price and copy it).

I've written a cursor, but it updates all of the rows in RidesForPricing with the same value. What am I missing? Any help would be appreciated. I'm using SQL Server 2008 R2.

DECLARE @CompanyID as int, @Date datetime, @SourceId as int, @DestinationId as int, @PriceOffice as nvarchar(250)

DECLARE @RidesForPricingCursor as CURSOR;
SET @RidesForPricingCursor = CURSOR FOR
SELECT CompanyID, Date, SourceId, DestinationId, PriceOffice
FROM dbo.vRidesForPricing
FOR UPDATE OF PriceOffice

OPEN @RidesForPricingCursor;
FETCH NEXT FROM @RidesForPricingCursor 
INTO @CompanyID, @Date, @SourceId, @DestinationId, @PriceOffice

CREATE TABLE #TEMP (CompanyID int, Date datetime, SourceId int, DestinationId int,
            PriceOffice nvarchar(250))

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #TEMP
SELECT CompanyID, Date, SourceId, DestinationId, PriceOffice
FROM dbo.vRidesDone 
WHERE CompanyID = @CompanyID AND SourceId = @SourceId AND DestinationId = @DestinationId
ORDER BY Date

IF (SELECT COUNT(*) FROM #TEMP) > 0
SET @PriceOffice = (SELECT TOP 1 PriceOffice FROM #TEMP)

UPDATE vRidesForPricing 
SET PriceOffice = @PriceOffice WHERE CURRENT OF @RidesForPricingCursor

FETCH NEXT FROM @RidesForPricingCursor 
INTO @CompanyID, @Date, @SourceId, @DestinationId, @PriceOffice;

END
DROP TABLE #TEMP

CLOSE @RidesForPricingCursor;
DEALLOCATE @RidesForPricingCursor;

Upvotes: 0

Views: 52

Answers (1)

marc_s
marc_s

Reputation: 754518

Why don't you just drop the cursor (which is a memory and resource hog and a prime performance killer) and just do a nice, set-based UPDATE:

UPDATE 
    rfp
SET
    PriceOffice = rd.PriceOffice
FROM 
    dbo.vRidesForPricing rfp
INNER JOIN
    dbo.vRidesDone rd ON rfp.CompanyID = rd.CompanyID
                      AND rfp.SourceId = rd.SourceId
                      AND rfp.DestinationId = rd.DestinationId

Does that update what you're expecting to get updated?

Upvotes: 1

Related Questions