CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
ASH
ASH

Reputation: 20302

How can I run an Update with a Row Counter

When I run this, I get a running count if IDs.

SELECT asset_id, ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY asset_id) AS cnt
FROM CPDG
ORDER BY asset_id

So, I turned that into an Update statement like this.

UPDATE CPDG
SET cnt = (SELECT ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY asset_id) 
           FROM CPDG)

But now I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How can I update a column with a running count?

Upvotes: 0

Views: 211

Answers (2)

forpas
forpas

Reputation: 164069

Create a CTE with your select query and then update on that:

with cte as (
  select asset_id, cnt,
    ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY asset_id) as rn
  from CPDG
)
update cte 
set cnt = rn

See a simplified demo.

Upvotes: 1

Stavr00
Stavr00

Reputation: 3314

You don't need a row_number() call ... 

UPDATE
  CPDG a
SET
  CPDG.cnt = (
    SELECT COUNT(*)
    FROM CPDG b
    WHERE a.asset_id > b.asset_id
  )

Upvotes: 0

Related Questions