CODEWITHSUNDEEP
sql-serversql-update
eyalb
eyalb

Reputation: 3022

update order column in a table but maintain the order

i have a table

id | title | F_ID  | order
----------------------------
1  | test 1| 1     | 44
2  | test 3| 1     | 3
3  | test 4| 1     | 1
4  | test 5| 2     | 1

i want to update order column to +10 for all rows that have F_ID 1 but keep the order

the result need to be

id | title | F_ID  | order
----------------------------
1  | test 1| 1     | 30
2  | test 3| 1     | 20
3  | test 4| 1     | 10
4  | test 5| 2     | 1

i can insert all rows that i want to update to temp table and then loop the rows and update every row in the real table by [id].

maybe there is a better option?

Upvotes: 2

Views: 2106

Answers (2)

jpw
jpw

Reputation: 44891

I think this should work:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table test (id int, title  varchar(49), F_ID int, [order] int)
insert test values 
(1  , 'test 1', 1, 44),
(2  , 'test 3', 1, 3),
(3  , 'test 4', 1, 1),
(4  , 'test 5', 2, 1)

Query 1:

update test
set [order] = new_order
from test t
inner join (
    select 
       id, 
       new_order = ROW_NUMBER() over (partition by f_id order by [order]) * 10
    from test t
    where f_id = 1
) t2 
on t.id = t2.id

Results:

Query 2:

select * from test

Results:

| ID |  TITLE | F_ID | ORDER |
|----|--------|------|-------|
|  1 | test 1 |    1 |    30 |
|  2 | test 3 |    1 |    20 |
|  3 | test 4 |    1 |    10 |
|  4 | test 5 |    2 |     1 |

Upvotes: 6

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Well there might be a better solution then this but you can try this by using recursive CTE.

;WITH updCTE
AS 
(
    SELECT 30 AS YourOrder, 1 AS id

    UNION ALL

    SELECT YourOrder - 10 AS YourOrder, id + 1 AS id
    FROM updCTE
    WHERE YourOrder > 1
)
UPDATE YourTable
SET [order] = YourOrder
FROM updCTE
JOIN YourTable ON updCTE.id = YourTable.id
WHERE YourTable.F_ID = 1
ORDER BY YourTable.id

Upvotes: 0

Related Questions