Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2011
    Posts
    12

    Post Unanswered: update query... help

    I have this data to update:

    Code:
    prs_ID		prt_ID	prs_startDate		prs_endDate		prs_reactivated
    60		37	2/24/2011 2:56:00 PM	2/24/2012 2:56:00 PM	False
    61		37	2/24/2012 2:56:00 PM	2/24/2013 2:56:00 PM	False
    62		37	2/24/2013 2:56:00 PM	2/24/2014 2:56:00 PM	False
    63		37	2/24/2014 2:56:00 PM	2/24/2015 2:56:00 PM	False
    will changed to

    Code:
    prs_ID		prt_ID	prs_startDate		prs_endDate		prs_reactivated
    60		37	2/24/2011 2:56:00 PM	2/24/2012 2:56:00 PM	False
    61		37	2/24/2012 2:56:00 PM	2/24/2013 2:56:00 PM	True
    62		37	2/24/2013 2:56:00 PM	2/24/2014 2:56:00 PM	True
    63		37	2/24/2014 2:56:00 PM	2/24/2015 2:56:00 PM	True

    Rule:
    1. Update the rest to True but not the first subscription which has the minimum prs_startDate.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    WITH CTE (prs_ID, 
    	prs_startDate,
    	RowNum)
    as 
    (SELECT prs_ID, 
    	prs_startDate,
    	ROW_NUMBER() OVER (ORDER BY prs_startDate) as RowNum
    FROM MyTable
    )
    UPDATE U
    SET U.prs_reactivated = CASE WHEN CTE.RowNum = 1 
    				THEN 'False' 
    				ELSE 'True' 
    			END
    FROM MyTable AS U 
    	INNER JOIN CTE ON
    		U.prs_ID = CTE.prs_ID
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •