how to do reverse SQL between?

Hello,
I have a sproc where I am trying to adjust a position field (named POS).  there is a unique constratint on the pos field. (it is a compoisite with a few other fields, but for this question it is not relevant)
So, let's say I want to move item position 27 to 23. I thought I would change the item with pos 27 to pos -99, then used this : (@pos = 27, @newpos = 23)
UPDATE positionexample SET pos = -99 WHERE pos = @pos
UPDATE positionexample SET pos = pos + 1 WHERE pos BETWEEN (@pos-1) AND (@newpos)
UPDATE positionexample SET pos = @newpos WHERE pos = -99

Now, since there is no item 27 (it was changed to 99), i was hoping that line 2 would
26 = 27
25 = 26
24 = 25
23 = 24

But , i get a index violation when trying to run this. The first line runs fine..it is the 2nd line where it fails.
melegant99Asked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
You will have to move the whole area out of sight first, if POS is a unique ID with unique index on it. That is:

update positionexample set pos = -pos where pos between @pos and @newpos
update positionexample set pos = -pos where pos between @newpos and @pos
update positionexample set pos = -pos+1 where -pos != @pos and pos < 0 and @newpos < @pos
update positionexample set pos = -pos-1 where -pos != @pos and pos < 0 and @newpos > @pos
update positionexample set pos = @newpos where -pos = @pos

Open in new window

0
 
cyberkiwiCommented:
UPDATE positionexample
SET pos = CASE
WHEN pos = @pos then @newpos
WHEN @pos < @newpos then pos + 1
ELSE pos - 1
END
WHERE pos between @pos and @newpos
0
 
cyberkiwiConnect With a Mentor Commented:
Oops.. please reverse direction

UPDATE positionexample
SET pos = CASE
WHEN pos = @pos then @newpos
WHEN @pos < @newpos then pos - 1
ELSE pos + 1
END
WHERE pos between @pos and @newpos
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
cyberkiwiCommented:
For your SQL, it should be

UPDATE positionexample SET pos = -99 WHERE pos = @pos
UPDATE positionexample SET pos = pos + 1 WHERE pos BETWEEN (@pos) AND (@newpos)
UPDATE positionexample SET pos = @newpos WHERE pos = -99

Otherwise it will try to update 22  (@pos-1) to 23 which will clash with the 3rd statement
0
 
melegant99Author Commented:
Sorry it took so long to get back.
0
 
cyberkiwiCommented:
Disappointing that an answer with 5 statements is preferred to one with 3 that elegantly shuffles the data using only one temp position (-99).
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
cyberkiwi,
what about move from 23 to 27? The 27 to 23 was an example only, so you cannot rely on the new pos being before the old one.
0
 
cyberkiwiCommented:
Hi qlemo

Better to .. than to open your mouth and remove all doubt.

WHEN @pos < @newpos then pos - 1 ELSE pos + 1

That takes care of the direction.  I see that the code is beyond you.  Better luck next time.
0
 
cyberkiwiCommented:
If you were commenting on #31433526 where I merely illustrate changes to the original question code for the exact direction used, then my apologies.
0
 
melegant99Author Commented:
Both solutions are viable. At the time, Qlemo's solution was easiest for me to understand since your third post cyber threw me. Plus, I was able to take Qlemo's answer condense it down to 3 lines with something I did in the code.
That is not to say when I have some time I will revist the sproc and may replace it with the case. For me and  this site I will award points to what worked for me at that time and what seems to me most clear and concise. Elegance is best but when you are in a jam it comes 2nd.  I am paying 15$ a month because you guys are the experts, not me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.