Change Position of Serial Number in SQL

Question

I have a table named students. the structure is given below

______________________________ 
AdmissionNo   RollNo   Name 
______________________________
1001              1     A  
1003              2     B  
1005              3     C  
1006              4     D  
1008              5     E

Now i want to change rollno 4 to 2 and increment forthcoming numbers

so the result should be like below

-------------------------------
AdmissionNo   RollNo   Name
-------------------------------
1001              1     A  
1006              2     D  
1003              3     B  
1005              4     C  
1008              5     E  
--------------------------------

How to attain this using sql Query.

Note: Question Edited as per 'The Impaler' said.Admission number is not changing.only Roll no change. The values in table are examples actual values are hundreds of records.

. . I removed the inconsistent database tags. Please tag only with the database you are really using. — Gordon Linoff, Jun 30 '20 at 14:47
Apart from changing the position you are also changing the `AdmissionNo`. Are you sure this is wise? It could lead to a lot of inconsistencies. — The Impaler, Jun 30 '20 at 14:51
' i want to change rollno 4 to 2 and ind increment forthcoming numbers; - But your result shows that the name changes for 2 and 2s name and subsequent names are pushed down 1. — P.Salmon, Jun 30 '20 at 14:56
@the-impaler Actually Admission No is not changing,thats my mistake question edited. — Chefzi Grand, Jul 01 '20 at 02:30
Found similar question and answer here.[link](https://stackoverflow.com/questions/812630/how-can-i-reorder-rows-in-sql-database) — Chefzi Grand, Jul 01 '20 at 03:50

score 0 · Answer 1 · answered Jun 30 '20 at 15:06

With the omission of a dialect, I have answered this in T-SQL, as I wanted a stab at this.

This isn't pretty, however, I use a couple of updatable CTE's to find the offset for the specific rows, and then update the needed rows accordingly:

USE Sandbox;
GO

CREATE TABLE dbo.YourTable (AdmissionNo int, Rollno tinyint, [Name] char(1));

INSERT INTO dbo.YourTable
VALUES(1001,1,'A'),
      (1003,2,'B'),
      (1005,3,'C'),
      (1006,4,'D'),
      (1008,5,'E');
GO

DECLARE @NewPosition tinyint = 2,
        @MovingName char(1) = 'D';

WITH Offsetting AS(
    SELECT *,
           COUNT(CASE Rollno WHEN @NewPosition THEN 1 END) OVER (ORDER BY RollNo ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) -
           COUNT(CASE [Name] WHEN @MovingName THEN 1 END) OVER (ORDER BY RollNo ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LagOffset       
    FROM dbo.YourTable),
NewNames AS(
    SELECT *,
           CASE RollNo WHEN @NewPosition THEN @MovingName
                                         ELSE LAG([Name],LagOffset) OVER (ORDER BY RollNo)
           END AS NewName
    FROM Offsetting)
UPDATE NewNames
SET [Name] = NewName;
GO

SELECT *
FROM dbo.YourTable;

GO

DROP TABLE dbo.YourTable;

score 0 · Answer 2 · answered Jun 30 '20 at 15:13

Not pretty but you could use some sub queries

DROP TABLE IF EXISTS T;
create table t
(AdmissionNo int,  RollNo int,  Name varchar(1)); 
insert into t values
(1001       ,       1   ,  'A'),  
(1003       ,       2   ,  'B'), 
(1005       ,       3   ,  'C'),  
(1006       ,       4   ,  'D'),  
(1008       ,       5   ,  'E');

select t.*,
         case when rollno = 2 then (select name from t where rollno = 4)
         when rollno > 2 and 
               rollno <> (select max(rollno) from t) then (select name from t t1 where t1.rollno < t.rollno order by t1.rollno desc limit 1)
         else name
         end    
from t;

+-------------+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1001 |      1 | A    | A                                                                                                                                                                                                                                       |
|        1003 |      2 | B    | D                                                                                                                                                                                                                                       |
|        1005 |      3 | C    | B                                                                                                                                                                                                                                       |
|        1006 |      4 | D    | C                                                                                                                                                                                                                                       |
|        1008 |      5 | E    | E                                                                                                                                                                                                                                       |
+-------------+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.001 sec)

Strawberry · Answer 3 · 2021-04-16T08:49:09.177

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(admission_no INT NOT NULL PRIMARY KEY
,roll_no INT NOT NULL
,name CHAR(1) NOT NULL
);

INSERT INTO my_table VALUES
(1001,1,'A'),
(1003,2,'B'),
(1005,3,'C'),
(1006,4,'D'),
(1008,5,'E');

SELECT *
     , CASE WHEN roll_no = 4 THEN 2 
            WHEN roll_no >= 2 AND roll_no < 4 THEN roll_no + 1 
            ELSE roll_no END x FROM my_table;
+--------------+---------+------+---+
| admission_no | roll_no | name | x |
+--------------+---------+------+---+
|         1001 |       1 | A    | 1 |
|         1003 |       2 | B    | 3 |
|         1005 |       3 | C    | 4 |
|         1006 |       4 | D    | 2 |
|         1008 |       5 | E    | 5 |
+--------------+---------+------+---+
5 rows in set (0.00 sec)

...or, as an update...

UPDATE my_table x
  JOIN 
     ( SELECT *
            , CASE WHEN roll_no = 4 THEN 2 
                   WHEN roll_no >= 2 AND roll_no < 4 THEN roll_no + 1 
                   ELSE roll_no END n  
         FROM my_table
     ) y
    ON y.admission_no = x.admission_no
   SET x.admission_no = y.n;

You'd probably want to extend this idea to deal with the fact that rows can be dragged up and down the list, so something like this...

SET @source = 1, @target = 5;

SELECT *
     , CASE WHEN roll_no = GREATEST(@source,@target) THEN LEAST(@source,@target)
            WHEN roll_no >= LEAST(@source,@target) AND roll_no < GREATEST(@source,@target) THEN roll_no + 1 
            ELSE roll_no END x 
  FROM my_table;

score -1 · Answer 4 · answered Jun 30 '20 at 15:13

Try this below query

; with cte as (select a.AdmissionNo, a.RollNo, b.Name from student a
join student b on a.RollNo=b.RollNo+1
where a.RollNo between 3 and 4

union all

select a.AdmissionNo, a.RollNo, b.Name from student a
left join student b on a.RollNo+2=b.RollNo
where a.RollNo=2)
update a set a.Name = b.name
from student a
join cte b on a.rollno=b.rollno

Change Position of Serial Number in SQL

4 Answers4

Linked