Update first row in groups, set rest to other val

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 2008 Forums

Transact-SQL (2008)

Update first row in groups, set rest to other val

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2013-09-17 : 13:54:25

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-17 : 14:32:32

Here is one way. In the future, please post your data in a consumable format so we cna help you better/faster

DECLARE @Foo TABLE(ID INT, Type CHAR(1), [Date] DATETIME)
INSERT @Foo VALUES
(17494,NULL,'2013-09-04 15:00:00.000'), 
(17577,NULL,'2013-09-04 15:00:00.000'), 
(17639,NULL,'2013-09-04 17:00:00.000'), 
(17701,NULL,'2013-09-04 17:00:00.000')


UPDATE 
	A
SET 
	Type = CASE WHEN RowNum = 1 THEN 'F' ELSE 'V' END
FROM
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY [Date]) AS RowNum
	FROM
		@Foo
) AS A

SELECT * FROM @Foo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources