How to Sort Values in Columns and Update Table

How to sort values in columns and update table?

You would have to use a second table

1. create a new table games2 with the same structure as your games table, making sure the ID is auto-incrementing

   CREATE TABLE `games2` LIKE `games`;
   

2. copy the data, sorted, into games2

   INSERT INTO `games2` (`Name`, `SomeDescription`) SELECT `Name`, `SomeDescription` FROM `games` ORDER BY `Name`
   

3. drop or move the old table

   -- DROP TABLE `games`;
   -- or
   RENAME TABLE `games` TO `games1`;
   

4. rename new table to old name

   RENAME TABLE `games2` TO `games`;
   

These steps will result in what you want.

SQL - UPDATE TABLE and ORDER BY?

Tables don't inherently have an order; you don't have to update them into any particular order.

What you do is choose the order of what you SELECT from the table. Then it can be in any order you choose!

Example:

SELECT * FROM movement  
ORDER BY timestamp; 

But then somewhere else maybe you want to:

SELECT * FROM movement  
ORDER BY timestamp DESCENDING; 

MSSQL Sort by 2 columns then update a sort column

You want ROW_NUMBER:

SELECT *, ROW_NUMBER() OVER (PARTITION BY Category, Date ORDER BY Item) [Order]
FROM yourTable
ORDER BY Date, Category;

Sort column values to match order of values in another table column

So you need to update Column2 with the row-number according toColumn1?

You can use ROW_NUMBER and a CTE:

WITH CTE AS 
(
    SELECT Column1, Column2, RN = ROW_NUMBER() OVER (ORDER BY Column1)
    FROM MyTable
)
UPDATE CTE SET Column2 = RN;

This updates the table MyTable and works because the CTE selects a single table. If it contains more than one table you have to JOIN the UPDATE with the CTE.

Demo

SQL Server: UPDATE a table by using ORDER BY

No.

Not a documented 100% supported way. There is an approach sometimes used for calculating running totals called "quirky update" that suggests that it might update in order of clustered index if certain conditions are met but as far as I know this relies completely on empirical observation rather than any guarantee.

But what version of SQL Server are you on? If SQL2005+ you might be able to do something with row_number and a CTE (You can update the CTE)

With cte As
(
SELECT id,Number,
ROW_NUMBER() OVER (ORDER BY id DESC) AS RN
FROM Test
)
UPDATE cte SET Number=RN

---

Related Topics