CODEWITHSUNDEEP
sqlsql-serversql-updatecommon-table-expression
Abdul Wahab
Abdul Wahab

Reputation: 1

unable to use alias in Update statment in sql

I working of update query for with the help of CTEs. Actually I want to update a table records, on the basis of duplicate records I just want to update one one row among those duplicate rows. My code is mentioned below:

 with toupdate as (
  select c.*,
         count(*) over (partition by c.ConsumerReferenceNumber) as cnt,
         max(c.ID) over (partition by  c.ID) as onhand_value
  from [dbo].[tbl_NADRA_CPS] c
 )
 update [dbo].[tbl_NADRA_CPS]
 set StatusID = 38
 where cnt > 1;

I am unable to use 'cnt' in my update where clause. Thanks in advance.

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you want to update one row among the duplicates, then your query will not do that. Instead:

 with toupdate as (
       select c.*,
              count(*) over (partition by c.ConsumerReferenceNumber) as cnt,
              row_number() over (partition by c.ConsumerReferenceNumber order by c.ID) as seqnum
       from [dbo].[tbl_NADRA_CPS] c
      )
 update toupdate
    set StatusID = 38
 where cnt > 1 and seqnum = 1;

The cnt > 1 gets reference numbers with more than one row. The seqnum = 1 ensures that just one is updated.

Upvotes: 0

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Because cnt is a field of your CTE, not of [dbo].[tbl_NADRA_CPS]

with toupdate as (
  select c.*,
         count(*) over (partition by c.ConsumerReferenceNumber) as cnt,
         max(c.ID) over (partition by  c.ID) as onhand_value
  from [dbo].[tbl_NADRA_CPS] c
 )
 update toupdate
 set StatusID = 38
 where cnt > 1;

Upvotes: 2

Related Questions