CODEWITHSUNDEEP
t-sqlsql-server-2008-r2group-byupdates
A.G.
A.G.

Reputation: 2149

T-SQL: Update a field in the first row of every group to one value and set the rest to another?

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 Type field on the first row of each date group and set it to 'F' and the rest (could be one could be many) set those to 'V'

Result:

ID   |Type|Date
17494|F   |2013-09-04 15:00:00.000  
17577|V   |2013-09-04 15:00:00.000  
17639|F   |2013-09-04 17:00:00.000  
17701|V   |2013-09-04 17:00:00.000

Upvotes: 0

Views: 2535

Answers (3)

Viji
Viji

Reputation: 2629

Hi There is no direct query to achieve this. But we can achieve this by work around

With #temptable as(
select Row_number() over( PARTITION BY date1 ORDER BY date1) Rank, *
from tablea)

update TableA set Type = 'F'
where id in (select Id from #temptable where Rank = 1)

update TableA set Type = 'V'
where Type is null

select * from TableA

I checked in SQLFiddle,its working http://sqlfiddle.com/#!3/bfb05/6/0

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

UPDATE T
SET Type = CASE WHEN IDD = 1 THEN 'F' ELSE 'V' END
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Date ORDER BY ID) IDD , * FROM tbl) T;

SQL FIDDLE DEMO

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Solution for SQL2005+:

DECLARE @MyTable TABLE
(
    ID      INT NOT NULL,
    [Type]  CHAR(1) NULL,
    [Date]  DATETIME NOT NULL 
);
INSERT  @MyTable (ID, [Type], [Date])
SELECT 17494, NULL, '2013-09-04 15:00:00.000' UNION ALL
SELECT 17577, NULL, '2013-09-04 15:00:00.000' UNION ALL
SELECT 17639, NULL, '2013-09-04 17:00:00.000' UNION ALL
SELECT 17701, NULL, '2013-09-04 17:00:00.000';

WITH MyTableWithRowNum
AS
(
    SELECT  *, ROW_NUMBER() OVER(PARTITION BY x.[Date] ORDER BY x.ID) AS RowNum
    FROM    @MyTable x
)
UPDATE  MyTableWithRowNum
SET     [Type] = CASE WHEN RowNum = 1 THEN 'F' ELSE 'V' END;

SELECT * FROM @MyTable;

Results:

ID          Type Date
----------- ---- -----------------------
17494       F    2013-09-04 15:00:00.000
17577       V    2013-09-04 15:00:00.000
17639       F    2013-09-04 17:00:00.000
17701       V    2013-09-04 17:00:00.000

Upvotes: 1

Related Questions