Sql-server – Update Top 1 Row Only

sql serversql-server-2008-r2t-sqlupdate

I have a table where I need to update only 1 field per employee. Below is sample ddl and my desired output, how should an update query be written in order to update only the Top 1 row?

Declare @Table1 Table (empName varchar(100), fieldtoupdate varchar(10))

Insert Into @Table1 (empName) VALUES ('Blue'), ('Blue'), ('Blue'), 
('Blue'), ('Blue'), ('Blue'), ('Red'), ('Red'), ('Red'), ('Red'), 
('Green'), ('Green'), ('Green')

Select * from @Table1

empName fieldtoupdate
Blue    Top 1
Blue    
Blue    
Blue    
Blue    
Blue    
Red     Top 1
Red 
Red 
Red 
Green   Top 1
Green   
Green

Best Answer

I would use a CTE here to provide row numbers to your table data (partitioned by the empName). That way you can update the cte, which has the advantage of actually updating the base table.

No bear in mind that because you have no ordering column there is no guarantee of which row would actually get updated (you would want more restrictive sorting conditions around this), however for a base example you can use the following.

DECLARE @Table1 TABLE
    (
      empName VARCHAR(100) ,
      fieldtoupdate VARCHAR(10)
    );

INSERT  INTO @Table1
        ( empName )
VALUES  ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Green' ),
        ( 'Green' ),
        ( 'Green' );
WITH    cteTop1Table ( RowNum, empName, fieldtoupdate )
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY empName ORDER BY empName ) AS RowNum ,
                        empName ,
                        fieldtoupdate
               FROM     @Table1
             )
    UPDATE  cteTop1Table
    SET     fieldtoupdate = 'Top 1'
    WHERE   RowNum = 1;

SELECT  *
FROM    @Table1;


empName fieldtoupdate
Blue    Top 1
Blue    NULL
Blue    NULL
Blue    NULL
Blue    NULL
Blue    NULL
Red     Top 1
Red     NULL
Red     NULL
Red     NULL
Green   Top 1
Green   NULL
Green   NULL
Related Question