update query... help

neo_phyte · #1 (**permalink**) 02-25-11, 03:57

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.

Wim · #2 (**permalink**) 02-25-11, 08:31

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

Internet Services

Web Development

Digital Marketing

Consumer Tech