1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,6
2) Now, Change the existing Order of A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
3)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6
Your expected results do not make sense. In your example after making changes, you have A5 and A6 repeating with two different order values. What happened to A1 and A4?
Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
If you are trying to allow users to customize their sort order, you should add a user id field to your table.
Your proc to update the table should simply be an update statement setting the new order values that the user provided from the GUI. The front end application should do presentation layer validation to ensure the user input a number in correct format. Let them enter the numbers for their sort order and then save that list. It looks like you are over complicating this.