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
I have this table:

ID| Type|Date
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

I'd like to Group by Date then Update the first row of each date group and set Type field to 'F' and the rest (could be one could be many) set those to 'V'

--PhB

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
Go to Top of Page
   

- Advertisement -