Update Query Using Row Number

Johnathan Simpson 586 Reputation points
2021-10-03T01:35:58.083+00:00

I'm trying to update a table and ONLY update rows where the row count is >= 2, leave row 1 as is and do not update. I have this query, but it is updating everything in my table

;With rc As
(
    Select [order number]
    ,Row_Number() Over (Partition by [order number] Order By [order number]) rn
    FROM Testing
)
UPDATE pd
SET pd.pd = '0.00'
FROM Testing pd
INNER JOIN rc rc
ON rc.od = pd.od
where rc.rn >= 2

Thisis sample DDL

Create Table Testing
(
    od varchar(100)
    ,pd decimal(16,4)
)
Insert Into Testing Values
('abc123', '12.00'), ('abc123', '12.00'), ('abc123', '12.00')
,('rlm333', '54.00'), ('esy232', '43.00')

So when a select statement is run my desired result set after the update is:
abc123 12.00
abc123 0.00
abc123 0.00
rlm333 54.00
esy232 43.00

Notice that in the sample DDL the first row number retains the value for pd but all other values were set to 0.00. In my production database there are over 4500 rows that I need this update performed to that have exact same set-up.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. Ronen Ariely 15,101 Reputation points
    2021-10-03T04:16:16.807+00:00

    Good day,

    There is a very common mistake which even experts in the forum do from time to time, which is that they forget that table is a SET of UNORDERED rows. For small tables in most cases when we use SELECT then the server returns the rows in the same order that we INSERT them, but this is not guaranteed!

    It is well documented that without using ORDER BY the order is not guaranteed.

    In your case according to the DDL+DML which you provided, there is no way to answer the question as is.

    First of all, you don't have [order number] column in your sample DDL but only od and pd. Moreover, it's NOT clear what you use for the sorting or partitioning. According to your DDL the table has no column which can be used for definition of the order, which mean we cannot know which rows should be updated

    For the sake of the discussion I added column id INT identity(1,1), in order to present a solution in case you had such column which defines the order of the rows.

    Please check if this help you

    use tempdb
    GO
    DROP TABLE IF EXISTS Testing
    GO
    Create Table Testing
    (
     id INT identity(1,1),
     od varchar(100)
     ,pd decimal(16,4)
    )
     Insert Into Testing Values
     ('abc123', '12.00'), ('abc123', '12.00'), ('abc123', '12.00')
     ,('rlm333', '54.00'), ('esy232', '43.00')
    GO
    
    select * from Testing
    GO
    
    ;with MyCTE as (
     select id, od, pd, RN = ROW_NUMBER() OVER(PARTITION BY od ORDER BY id)
     from Testing
    )
    UPDATE Testing
     SET pd = 0
     FROM Testing t1
     INNER JOIN MyCTE t2 ON t1.id = t2.id
     WHERE RN>1
    GO
    
    select * from Testing
    GO
    
    2 people found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 114.5K Reputation points
    2021-10-03T08:08:08.76+00:00

    Try a shorter script too:

    ;
    with rc As
    (
        select *, row_number() over (partition by pd order By (select null)) rn
        from Testing
    )
    update rc
    set pd = '0.00'
    where rn >= 2
    
    1 person found this answer helpful.