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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
cyberkiwiCommented:
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
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Qlemo"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Qlemo"Batchelor", 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.