I have a db table containing a column display_order. The data looks like this:
2
4
7
10
12
I want to update the same db column and it should look like this:
1
2
3
4
5
Please suggest some easy SQL code.
-
does your table have an identity column?Laurence Burke– Laurence Burke2012-04-04 14:17:28 +00:00Commented Apr 4, 2012 at 14:17
Add a comment
|
3 Answers
Have a look into ROW_NUMBER(), this will help you here.
e.g. demo that won't update your data, but will show you the current order and the new order based on ROW_NUMBER
SELECT display_order AS CurrentDisplayOrder,
ROW_NUMBER() OVER (ORDER BY display_order) AS NewDisplayOrder
FROM YourTable
ORDER BY display_order
If that produces what you'd expect, then you can just switch it into an UPDATE statement.
2 Comments
Bridge
@LaurenceBurke Depends what you mean by "flagged as inactive" - if that's another column, you should be able to put a
where clause in before the Order by.
Karan
can you provide me the update statement... i tried with this..UPDATE t SET t.display_order = ROW_NUMBER() OVER (ORDER BY display_order) FROM [Folder] t and t.folder_stub='10000000-0000-0000-0000-000000000001' but it is throwing the error to me as "Windowed functions can only appear in the SELECT or ORDER BY clauses.".
Expanding on AdaTheDev's idea - using a CTE (Common Table Expression) makes it really easy to see how to use the actual UPDATE to update your table. I'm using a table variable @work here to simulate your existing table - just replace my table variable with your own table name:
DECLARE @work TABLE (display_order INT)
INSERT INTO @work VALUES(2)
INSERT INTO @work VALUES(4)
INSERT INTO @work VALUES(7)
INSERT INTO @work VALUES(10)
INSERT INTO @work VALUES(12)
SELECT * FROM @work
;WITH UpdateTable AS
(
SELECT
display_order, new_order = ROW_NUMBER() OVER (ORDER BY display_order)
FROM @work
)
UPDATE @work
SET display_order = u.new_order
FROM @work w
INNER JOIN UpdateTable u ON w.display_order = u.display_order
SELECT * FROM @work
1 Comment
Steve Kass
Marc, You can do this even more simply and transparently by using the CTE table itself as the target of the update: ;WITH UpdateTable AS (SELECT display_order, new_order = ROW_NUMBER() OVER (ORDER BY display_order) FROM @work) UPDATE UpdateTable SET display_order = new_order . (If there is any possibility the display_order column is not unique, it's important to add a unique column to the ORDER BY clause so that the ROW_NUMBER() value will be deterministic.)
Without CTE (but needs some key in the table)
declare @tbl table(id int primary key identity(1,1),Value int)
insert @tbl values(2)
insert @tbl values(5)
insert @tbl values(3)
select * from @tbl
select *, ROW_NUMBER() over(order by Value) from @tbl order by id
update @tbl set Value = result from @tbl tbl
inner join (select id, ROW_NUMBER() over(order by Value) result from @tbl ) hlp on tbl.id =hlp.ids
select * from @tbl
