How to use row_number() in SQL Server

I want to update row data where the row_number of the column (p_id) is 1.. but this syntax开发者_C百科 is providing error:

update app1 
set p_id = 1 
where Row_Number() = 1 over(p_id) 

You can't use ROW_NUMBER() directly - you need to e.g. use a CTE (Common Table Expression) for that:

;WITH DataToUpdate AS
(
    SELECT
       SomeID,
       p_id,
       ROW_NUMBER() OVER(ORDER BY .......) AS 'RowNum'
    FROM
       dbo.app1
)
UPDATE DataToUpdate
SET p_id = 1
WHERE 
   RowNum = 1

In order to use the ROW_NUMBER function, you also need at least an ORDER BY clause to define an order by which the rows are ordered.

From your question, it's not very clear what criteria (column) you want to order by to determine your ROW_NUMBER(), and it's also not clear what kind of column there is to uniquely identify a row (so that the UPDATE can be applied)

This will update only the first employee of that age. May be used as a lottery type logic

create table emp(name varchar(3),Age int, Salary int, IncentiveFlag bit)

insert into emp values('aaa',23,90000,0);
insert into emp values('bbb',22,50000,0);
insert into emp values('ccc',63,60000,0);
insert into emp values('ddd',53,50000,0);
insert into emp values('eee',23,80000,0);
insert into emp values('fff',53,50000,0);
insert into emp values('ggg',53,50000,0);

update A
set IncentiveFlag=1
from 
(
        Select row_number() over (partition by Age order by age ) AS SrNo,* from emp
)A
where A.SrNo=1

TO Delete duplicates ;WITH CTE(Name,Address1,Phone,RN) AS ( SELECT Name,Address1,Phone, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) AS RN ) DELETE FROM CTE WHERE RN > 1