Bimal Das Bimal Das - 4 months ago 11
SQL Question

How to toggle between two values of concurrent table rows in sql

I have a table name

[NavBar]
with these columns:

Id [int]
Name [nvarchar]
DisplayOrder [int]


Sample data:

Id Name DisplayOrder
---------------------------
100 Home 1
101 Products 2
102 Contact 3
103 Career 4


How do I perform update operation to toggle between two rows
DisplayOrder
of
two concurrent rows
(order by
Displayorder
) ?

For example
Contact = 4, Career = 3


Expected output:

100 Home 1
101 Products 2
102 Contact 4
103 Career 3


Input parameter:
Id
of one row only

Answer

If you are updating id = 102 (and then 103)

 drop table navBar;
 create table navBar
 (Id [int],
Name [nvarchar] (200),
DisplayOrder [int]);

insert into navBar
values
 (100,   'Home'      ,  1),
 (101,   'Products',    2),
 (102 ,  'Contact',     3),
 (103  , 'Career',      4);

 declare @id int;

 set @id = 102

 ; with t as(
 select id,  name, DisplayOrder, 
        lag(id) over(order by DisplayOrder) lgid, 
        lag(DisplayOrder) over(order by DisplayOrder) lgDisplayOrder,
        lead(DisplayOrder) over(order by DisplayOrder) ldDisplayOrder
 from navBar)
 update t
   set DisplayOrder = case @id when id then ldDisplayOrder else lgDisplayOrder end
 where (@id = id and ldDisplayOrder is not null)
   or  (@id = lgid);


 select * from navBar;

OUTPUT

Id  Name    DisplayOrder
100 Home    1
101 Products    2
102 Contact 4
103 Career  3