CODEWITHSUNDEEP
sqlsql-serversql-updatesql-order-byrow-number
Andrew
Andrew

Reputation: 121

How to set an int value depending on alphabetical sort order

I'm a SQL noob and I need syntax help on how do I use a SET command to assign the values of my SortOrder Column. The below code does not Update the table but it's how I would like it to look (all SortOrder values are set to 0). I could go through manually but looking for a quick alternative (the data set is a bit bigger). 

Select ROW_NUMBER() OVER (ORDER BY Name ASC) AS SortOrder, Name
From DesignColours

SortOrder |Name
1          Beige
2          Black
3          Blue
4          Brown
5          Copper

Thanks for the help

Upvotes: 3

Views: 1488

Answers (2)

Daniel Marcus
Daniel Marcus

Reputation: 2686

update a
set a.SortOrder = b.SortOrder
from DesignColours a
join 
(Select ROW_NUMBER() OVER (ORDER BY Name ASC) AS SortOrder, Name
   From DesignColours)b
on a.Name = b.Name

Upvotes: 5

Eray Balkanli
Eray Balkanli

Reputation: 7990

You can benefit from cte like below, assuming you are using SQL Server and your original table has already a column like SortOrder:

;with cte (SortOrder, Name) as (
   Select ROW_NUMBER() OVER (ORDER BY Name ASC) AS SortOrder, Name
   From DesignColours
)
update d
set d.SortOrder = cte.SortOrder
from DesignColours d
inner join cte on d.Name = cte.Name

If there is no SortOrder column already existing, you can do:

ALTER TABLE DesignColours
ADD SortOrder int
GO

The regular usage of update statement is:

UPDATE tablename
set columnName = yourvalue
where <yourrule>

Ref: https://www.w3schools.com/sql/sql_update.asp

Upvotes: 5

Related Questions