October 21, 2009 at 10:29 pm
Hi,
I have a table 'STATUS' with single column called 'flag',in that all the values are filled with 'Y'.I need the query to update 10th row value to 'S'.How to do this?How to update the value using the row number in sql?
October 22, 2009 at 1:02 am
Hi there,
SQL Server 2005 has a ROW_NUMBER() function and you would use it something like this:
SELECT ROW_NUMBER() OVER (ORDER BY FLAG) AS ROWID, * FROM [Status]
For an update, it seems we need to use a CTE:
WITH [STATUS PLUS ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY (ORDER BY FLAG)) AS ROWID, * FROM [Status])
UPDATE [STATUS PLUS ROWID] SET Flag = 'S' WHERE ROWID = 10
However, should you wish to update in the future, the ORDER BY clause might make things tricky for you.
The only way I found to get around this is to ORDER BY (SELECT 0):
WITH [STATUS PLUS ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROWID, * FROM [Status])
UPDATE [STATUS PLUS ROWID] SET Flag = 'S' WHERE ROWID = 10
Let me know if you find another way to do this please 🙂
October 22, 2009 at 1:03 am
This is something what you would like to do, but its meaningless.
CREATE TABLE #tSTATUS (FLAG varchar(1))
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
INSERT INTO #tStatus VALUES ('Y')
Select *, ROw_Number() OVER (Order by flag) as row_no
from #tSTATUS
UPDATE R
SET FLAG = 'S' -- Select *
FROM (Select *, ROw_Number() OVER (Order by flag) as row_no
from #tSTATUS
) R
Where row_no = 10
Select * From #tStatus
This is not how the rows are stored inside database. you need to ask;10th row based on which order? if there is no order constraint then there is no point! Hope it helps.
Please give the complete picture on what you are trying to achieve or what is the business justification for what you are doing then we might suggest a better way. Thanks.
Ohh sorry, dint see the reply from Diamondgm.
---------------------------------------------------------------------------------
October 22, 2009 at 1:21 am
Hi,
Thank you for your reply,It works fine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply