CODEWITHSUNDEEP
sql-server
A.Bahrami
A.Bahrami

Reputation: 81

Windowed functions can only appear in the SELECT or ORDER BY clauses-update in cursor

Declare @Customerid int 
DECLARE ChangeCustomerName CURSOR FOR 
select customerid from customer 
OPEN ChangeCustomerName
FETCH NEXT FROM ChangeCustomerName into @Customerid
WHILE @@fetch_status = 0
BEGIN

  update customer set customername ='Customer'
    +convert (varchar(10),ROW_NUMBER() OVER(ORDER BY customerid ASC)) 
    where customerid=@Customerid

   FETCH NEXT FROM ChangeCustomerName into @Customerid
END
close ChangeCustomerName
deallocate ChangeCustomerName

Windowed functions can only appear in the SELECT or ORDER BY clauses-update in cursor

Upvotes: 6

Views: 7290

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

Not sure why you are creating customer names.

Your current approach may work for existing records, but for new entries you have run the update query again and there are possibilities that the same customer gets different customer name after some deletes. So I suggest you to create a computed column.

You can make the customername as computed column 

Alter table customer 
add customername as 'Customer'+convert (varchar(10),customerid) PERSISTED

Note: It might not be sequential still it will be unique if customerid is unique

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You seem to be trying to set customer names to sequential values. For this purpose, you don't need a cursor! Just something like this:

with toupdate as (
      select c.*, row_number() over (order by customerid) as seqnum
      from customer c
     )
update toupdate
    set customername = 'Customer' + convert(varchar(10), seqnum);

You should avoid cursors whenever you can. Set-based operations are more efficient and often result in simpler code.

Upvotes: 11

Related Questions