question

I am trying to use a cursor to update a value in the PAYG column. The cursor should select just one value for PAYG based on the date that is immediately greater than the date from the main table it is comparing with. But it seems to update all the rows with the same maximum value from the table. I have tried the 'SELECT TOP1*' statement with hard coded values and this seems to do as required. But i am not sure why it is grabbing the same value. The query is as below:-

DECLARE

@REF char(8),

@Start_Date date

,@PAYG char(10)

IF OBJECT_ID('TE_Cursor') IS NOT NULL -- Drop cursor first in case it exists already

DROP PROC TE_Cursor

-- Get all PHIST Details

DECLARE TE_Cursor CURSOR FOR

SELECT REF, Start_Date, PAYG

FROM Test_Emp_Details ORDER BY REF, D580_Start_Date ASC

OPEN TE_Cursor

FETCH NEXT FROM TE_Cursor

INTO

@REF,

@Start_Date

,@PAYG

WHILE ( @@FETCH_STATUS = 0 )

BEGIN

UPDATE [Test_Emp_Details]

SET PAYG = ( SELECT TOP 1 PAYG FROM PGHIST_PAYG

WHERE REF = @REF

AND EFFECT_DATE >= @Start_Date

ORDER BY EFFECT_DATE DESC

)

WHERE REF = @REF

FETCH NEXT FROM TE_Cursor INTO @REF, @Start_Date, @PAYG

END

CLOSE TE_Cursor

DEALLOCATE TE_Cursor

END

--Test_Emp_DEtails

--Test_PGHIST_PAYG

Appreciate any help on this. Thanks