Update Query Using Row Number

Question

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.

Answer 1

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

Answer 2

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