Update Based on Nth Occurence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Based on Nth Occurence

Author

Topic

dkrumholz
Starting Member

2 Posts

Posted - 2009-12-18 : 09:11:08

I have seen SQL posted for selecting the Nth member of a group but I need to use those results in an update and I can't get the SQL straight -

I need to update a table I am going to export, of store records, where the table has 4 fields which contain the most recent robery dates in order (most recent in rdate1). The robbery data is in a detail table that has one record per store per robbery.

So my export table has one record per store with fields storeno, rdate1, rdate2, rdate3, rdate4 and my detail table has storeno and robbery date.

I want to accomplish this with 4 updates, one per date in my export table, without needing to fetch records in a loop.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-12-18 : 09:30:22

Try using ROW_Number() and CASE. Something like:


UPDATE E
SET rdate1 = D.rdate1
	,rdate2 = D.rdate2
	,rdate3 = D.rdate3
	,rdate4 = D.rdate4
FROM ExportTable E
	LEFT JOIN
	(
		SELECT StoreNo
			,MAX(CASE WHEN RowNum = 1 THEN RoberyDate END) AS rdate1
			,MAX(CASE WHEN RowNum = 2 THEN RoberyDate END) AS rdate2
			,MAX(CASE WHEN RowNum = 3 THEN RoberyDate END) AS rdate3
			,MAX(CASE WHEN RowNum = 4 THEN RoberyDate END) AS rdate4
		FROM
		(
			SELECT StoreNo
				,RoberyDate
				,ROW_NUMBER() OVER (PARTITION BY StoreNo ORDER BY RoberyDate DESC) AS RowNum
			FROM RoberyDetail
		) D1
		GROUP BY StoreNo
	) D ON E.StoreNo = D.StoreNo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-20 : 04:10:38

or use pivot


UPDATE E
SET rdate1 = D.rdate1
	,rdate2 = D.rdate2
	,rdate3 = D.rdate3
	,rdate4 = D.rdate4
FROM ExportTable E
	LEFT JOIN
	(
		SELECT StoreNo
			,[1] AS rdate1
			,[2] AS rdate2
			,[3] AS rdate3
			,[4] AS rdate4
		FROM
		(
			SELECT StoreNo
				,RoberyDate
				,ROW_NUMBER() OVER (PARTITION BY StoreNo ORDER BY RoberyDate DESC) AS RowNum
			FROM RoberyDetail
		) D1
PIVOT (MAX(RoberyDate) FOR RowNum IN ([1],[2],[3],[4]))p 
)D ON E.StoreNo = D.StoreNo

dkrumholz
Starting Member

2 Posts

Posted - 2009-12-20 : 15:50:21

thank you both!!

david r. krumholz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources