Change order of rows

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

+-------+--------------------------------------+------------------+--------------------------------------------------------+
| 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:

SecID

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


2 Answers
2

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

SecID

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;


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;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.