1

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   
  • Usually TOP is involved with some sort of ORDER BY, is there a certain row that you want updated (like with the most recent date) or are we just arbitrarily calling one record the "top" record? Are there any sort of unique identifiers on the table? – Kris Gruttemeyer Dec 8 '16 at 15:10
  • 1
    Is there anything in your table that we don't see that would indicate which is the top row? An insert date? Some order? What distinguishes on Blue from another? – tpet Dec 8 '16 at 15:11
  • There is no unique identifier or date that would signify the top. I just arbitrarily need 1 row for each empName updated. – SmallFries BigGuys Dec 8 '16 at 15:15
  • It can be done, but the DBA in me wants to know why? Is this homework or school related? If this is for some sort of production server process, I'd be wary. What does this 'fieldtoupdate' represent? How will it be used? – Kris Gruttemeyer Dec 8 '16 at 15:16
  • @KrisGruttemeyer - it is a process that takes number from production tables and inserts them into a "Review" table. Currently no date field is being pulled over, there are just tons of metrics broken down by saleid's – SmallFries BigGuys Dec 8 '16 at 15:22
2

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
  • If order doesn't matter, you can specify ORDER BY (SELECT NULL) inside OVER() clause too. – Paweł Tajs Dec 9 '16 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.