4

I want to update row data where the row_number of the column (p_id) is 1.. but this syntax is providing error:

update app1 
set p_id = 1 
where Row_Number() = 1 over(p_id)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user635545
  • 111
  • 1
  • 4
  • 12
  • 1
    Which row(s) are you trying to update? Not clear to me from the code you have supplied? – Martin Smith Apr 16 '11 at 11:12
  • 1
    @user, you need to explain in more detail what is the logic of the update you are trying to perform.. – Gabriele Petrioli Apr 16 '11 at 11:42
  • @martin and gaby---my logic is that if checkbox is checked then on button click i want to update the column of table app1 depending upon which checkbox was checked(in side gridview) – user635545 Apr 16 '11 at 18:34
  • @user635545 - You should just pass the primary key into the `where` clause of the `update` statement for this. Not use `row_number` at all. This is the most efficient way and also the correct way. Are you assuming (for example) that the 7th row in the grid will always be the 7th row in the table as ordered by `p_id`? What if a concurrent transaction has deleted a row? You could end up updating the wrong one. – Martin Smith Apr 16 '11 at 18:58
  • @martin lets say p_no is primary key and i use following code but there is no effect on table 'update app1 set p_no=1 where p_id=p_no' – user635545 Apr 16 '11 at 19:15
  • the above where clause don't make any sense – user635545 Apr 16 '11 at 19:17

3 Answers3

7

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • @andriy M: well a subselect isn't all that different from a CTE - but without any form of subselect or CTE, you cannot directly use a row_number() - right?? You definitely can't use it the way the OP tried to use it - agreed? – marc_s Apr 16 '11 at 12:08
  • 1
    I'm sorry, I had to delete my comment because I missed the 'update' bit of the original post. The direct use is out of question in that case. – Andriy M Apr 16 '11 at 12:11
  • 2
    By the way, I think it is possible to `UPDATE cte` if it references only one table. – Andriy M Apr 16 '11 at 12:12
  • The OP's syntax is just wrong. But did you actually try using `ROW_NUMBER()` in the WHERE clause? I didn't and I can't check it at the moment. – Andriy M Apr 16 '11 at 12:14
  • @Andriy M: I believe you're right on this second point - updated my answer accordingly! Thanks for pointing that out – marc_s Apr 16 '11 at 12:15
  • @Andriy M: yes - I tried - it gives error: `Msg 4108, Level 15, State 1, Line 4 Windowed functions can only appear in the SELECT or ORDER BY clauses.` – marc_s Apr 16 '11 at 12:16
  • @Andriy M: and the trouble is: if you use a `ROW_NUMBER() OVER() AS "RN"` in your SELECT, you cannot reference that "RN" column alias in the WHERE clause - you need at least a subselect or a CTE... – marc_s Apr 16 '11 at 12:17
  • Re: Error message – That's a shame. Still I suspected as much, and it's great to have it confirmed, thanks. – Andriy M Apr 16 '11 at 12:18
  • Sometimes you only need to return the ranking with the rest of the data, e.g. in a view, or to the client, even if those are rare use cases probably. – Andriy M Apr 16 '11 at 12:30
1

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
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
Subhash Lama
  • 433
  • 2
  • 12
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Apr 16 '11 at 12:30
  • @subhash--thank you very much sir...u have used UPDATE A...A indicate view? – user635545 Apr 16 '11 at 18:53
0

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

Sagar Byali
  • 11
  • 5