Change order of rows

Question

I have a table like this:

+-------+--------------------------------------+------------------+--------------------------------------------------------+
| SecID |               SecGuid                |    LevelType     |                      Description                       |
+-------+--------------------------------------+------------------+--------------------------------------------------------+
|     1 | 550239E9-01A1-4403-8133-834DD0EBD7EA | Administrator    | All Access Pass                                        |
|     2 | 181104C8-2241-4E51-B73D-59D5E4E8C22D | Advanced Author  | Edit, create, delete, archive, pricing, and agreements |
|     3 | 24E50493-854C-4C44-A074-E6D2EAAC5E5C | Mid Level Author | Edit, create, delete and archive                       |
|     4 | 306C087D-F86A-43F6-B57A-8C65F9E8337D | Author           | Edit and create                                        |
|     5 | 9B66AE7D-B704-4D75-8776-774A2CFC0C47 | Contibutor       | Edit                                                   |
|     6 | 35B0F735-E240-47D0-9E33-694D6197C765 | Browser          | View                                                   |
|     7 | E3606250-F5C5-4DED-B29F-53E6C6C91FDA | Banned           | no access                                              |
|     8 | B0A0F8E1-93F3-431F-A7BF-95F14C22703E | Low Level Author | Edit, create, delete, archive, Task assignation        |
+-------+--------------------------------------+------------------+--------------------------------------------------------+

But now I want to change my last row to be under third one and change SecID for 4 and of course change others under this row. So at the final, I want to achieve something like this:

+-------+--------------------------------------+------------------+--------------------------------------------------------+
| SecID |               SecGuid                |    LevelType     |                      Description                       |
+-------+--------------------------------------+------------------+--------------------------------------------------------+
|     1 | 550239E9-01A1-4403-8133-834DD0EBD7EA | Administrator    | All Access Pass                                        |
|     2 | 181104C8-2241-4E51-B73D-59D5E4E8C22D | Advanced Author  | Edit, create, delete, archive, pricing, and agreements |
|     3 | 24E50493-854C-4C44-A074-E6D2EAAC5E5C | Mid Level Author | Edit, create, delete and archive                       |
|     4 | B0A0F8E1-93F3-431F-A7BF-95F14C22703E | Low Level Author | Edit, create, delete, archive, Task assignation        |
|     5 | 306C087D-F86A-43F6-B57A-8C65F9E8337D | Author           | Edit and create                                        |
|     6 | 9B66AE7D-B704-4D75-8776-774A2CFC0C47 | Contibutor       | Edit                                                   |
|     7 | 35B0F735-E240-47D0-9E33-694D6197C765 | Browser          | View                                                   |
|     8 | E3606250-F5C5-4DED-B29F-53E6C6C91FDA | Banned           | no access                                              |
+-------+--------------------------------------+------------------+--------------------------------------------------------+

NOTE: Table have a constraint and SecID is a primary key:

ALTER TABLE [dbo].[SecAccess] ADD  CONSTRAINT [DF_SecAccess_SecGuid]  DEFAULT (newid()) FOR [SecGuid]

dup: https://stackoverflow.com/questions/812630/how-can-i-reorder-rows-in-sql-database — SuperShoot, Jul 25 '18 at 23:30
Possible duplicate of [How can I reorder rows in sql database](https://stackoverflow.com/questions/812630/how-can-i-reorder-rows-in-sql-database) — SuperShoot, Jul 25 '18 at 23:31
Some other examples of moving a row up, down and swapping rows are [here](https://stackoverflow.com/questions/48327539/manage-records-ordering/48329605#48329605). — HABO, Jul 26 '18 at 00:10

Olivier Jacot-Descombes · Accepted Answer · 2018-07-25T22:52:16.830

First, move down rows starting from 4:

UPDATE dbo.SecAccess SET SecID = SecID + 1 WHERE SecID > 3;

Then move the last row, now having the no. 9 to row 4:

UPDATE dbo.SecAccess SET SecID = 4 WHERE SecID = 9;

This assumes that SecIDis not an indentity column. If it is one then do

SET IDENTITY_INSERT dbo.SecAccess ON;
UPDATE dbo.SecAccess SET SecID = SecID + 1 WHERE SecID > 3;
UPDATE dbo.SecAccess SET SecID = 4 WHERE SecID = 9;
SET IDENTITY_INSERT dbo.SecAccess OFF;

See: SET IDENTITY_INSERT (Transact-SQL).

A solution giving you more flexibility is to introduce a new column solely used for sorting

ALTER TABLE dbo.SecAccess ADD SortOrder int;
UPDATE dbo.SecAccess SET SortOrder = CASE 
                                       WHEN SecID = 8 THEN 4
                                       WHEN SecID > 3 THEN SecID + 1
                                       ELSE SecID
                                     END;

I try but I get Cannot update identity column 'SecID' – Jul 25 '18 at 22:36 — , Jul 25 '18 at 22:36

score 0 · Answer 2 · answered Jul 25 '18 at 22:27

0

Maybe try:

update SecTable set SecID = 0 where SecID = 8;
update SecTable set SecID += 1 where SecID in (4,5,6,7);
update SetTable set SecID = 4 where SecID = 0;

answered Jul 25 '18 at 22:27

Seekwell74

11
3

No sir I have a constraint there, First I remove it, then I try what you said and I get 'Cannot update identity column 'SecID'.' – Jul 25 '18 at 22:33
obviously then, you'd need to set identity insert on and off again after. – Seekwell74 Jul 25 '18 at 23:40

Change order of rows

2 Answers2