We help IT Professionals succeed at work.

Help with update query

Hello EE,

I have a TreeView in vb.net and when I take a node and drag it over another node..I want the order to be change. it works well in vb.net because I remove and insert the node correctly. but in the database it needs to change also becasue If I reload the Treeview i need to see the new order.


My table looks like this:

Code   Description  Sequence

ABC          Test              1
ABC1        Test1            2
ABC2        Test2            3
ABC3        Test3            4


Lets say I take the node ABC and Drag on the node ABC3
I need ABC in the table to be "after" ABC3 I would like to modify the Sequence that the result is:

ABC1          1
ABC2          2
ABC3          3
ABC            4

same thing reverse... lets say now we take ABC3 move to node ABC1, I need to update to that ABC3 be After ABC1  like this:

ABC1       1
ABC3       2
ABC2       3
ABC         4


how can I do a query update for that?  Im confused with the new Sequence, old sequence +1 .... -1... update the others to fit....


can you help?
thanks
Comment
Watch Question

Lee WadwellProject Architect
BRONZE EXPERT

Commented:
You need to know the old and new position plus code ... then apply the logic for when the entry moved down or up in the list.  Assuming the old position is in the variable oldPos and new position is in newPos and the code was movedCode ... then logic like (pseudo code):

if newPos > oldPos then
  UPDATE seq_table
  SET Sequence = CASE WHEN Code = movedCode THEN newPos
                      ELSE Sequence - 1
  WHERE Sequence BETWEEN oldPos AND newPos
else
  UPDATE seq_table
  SET Sequence = CASE WHEN Code = movedCode THEN newPos
                      ELSE Sequence + 1
  WHERE Sequence BETWEEN newPos AND oldPos
endif

Open in new window

Jason SchlueterIT Manager
Commented:
You will need to know the index of the new position and whether it is being moved up or down.
Say that you are moving ABC from position 0 to position 3:
DECLARE @temp TABLE(
  code        CHAR ( 4 ),
  description CHAR ( 5 ),
  sequence    INT) 


insert into @temp values
('abc0','test0',0),
('abc1','test1',1),
('abc2','test2',2),
('abc3','test3',3)

select * from @temp

update @temp set sequence = sequence - 1 where sequence <= 3
update @temp set sequence = 3 where code = 'abc0'
select * from @temp order by sequence

Open in new window


If you were to move it from position 3 to 0, the comparison sign in the first update statement would be reversed (>=) and then we would add instead of subtract the sequence index.
DECLARE @temp TABLE(
  code        CHAR ( 4 ),
  description CHAR ( 5 ),
  sequence    INT) 


insert into @temp values
('abc0','test0',0),
('abc1','test1',1),
('abc2','test2',2),
('abc3','test3',3)

select * from @temp

update @temp set sequence = sequence + 1 where sequence >= 0
update @temp set sequence = 0 where code = 'abc3'
select * from @temp order by sequence

Open in new window

You could easily generate the SQL in a vb.net string and execute it that way.

Commented:
i think you have display order and on selection of specific code you want to replace it with new selected code sequence.

say as per your first example you want to interchange sequence of 'ABC' and 'ABC3'


-- first we will take input of new change sequence selected from dropdown lets say its 4 -----here i am setting it to 4
--[ You have to pas it as input in your storeprocedure ]


DEclare @newseq as int
declare @oldseq as int
declare @replacecode as varchar(255)

set @newseq=4


select @oldseq =Sequence from table_nme where code='ABC3'

select @replacecode =code from table_nme where Sequence =@newseq

Update table_nme set Sequence=@newseq where code='ABC3'
update table_nme set Sequence=@oldseq where code=@replacecode

Author

Commented:
Thanks!
Project Architect
BRONZE EXPERT
Commented:
I am sorry but you NEED to put the boundary on the update on the updates with the +1 or -1 to limit the updates being between the old position and the new position.  This is what I did in my example.  In the code from @JasonSchlueter it only uses the boundary on the new position end.
Try the @JasonSchlueter code where it is not moving to/from a beginning i.e.

Say that you are moving ABC1 from position 1 to position 2:
DECLARE @temp TABLE(
  code        CHAR ( 4 ),
  description CHAR ( 5 ),
  sequence    INT) 


insert into @temp values
('abc0','test0',0),
('abc1','test1',1),
('abc2','test2',2),
('abc3','test3',3)

select * from @temp

update @temp set sequence = sequence - 1 where sequence <= 2
update @temp set sequence = 2 where code = 'abc1'
select * from @temp order by sequence

Open in new window


or ... move it from position 2 to 1
DECLARE @temp TABLE(
  code        CHAR ( 4 ),
  description CHAR ( 5 ),
  sequence    INT) 


insert into @temp values
('abc0','test0',0),
('abc1','test1',1),
('abc2','test2',2),
('abc3','test3',3)

select * from @temp

update @temp set sequence = sequence + 1 where sequence >= 1
update @temp set sequence = 1 where code = 'abc2'
select * from @temp order by sequence

Open in new window

Author

Commented:
oh youre right
i will reopen to fix points
Jason SchlueterIT Manager

Commented:
I'm glad that I could assist.
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Another possibility would be to do the original Sequence assignments 10000 apart, then just split the difference when moving it.  You can use ROW_NUMBER() OVER(...) to assign sequential numbers when subsequently reading the table.

ABC          Test             10000
ABC1        Test1            20000
ABC2        Test2            30000
ABC3        Test3            40000

To move ABC to between ABC2 and ABC3:

UPDATE dbo.tablename
SET sequence = 35000
WHERE code = 'ABC'

Explore More ContentExplore courses, solutions, and other research materials related to this topic.