Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Update lines in PK

Posted on 2014-04-22
4
Medium Priority
?
301 Views
Last Modified: 2014-04-22
In my table the PK is ORDER_NUMBER,LINE_NUMBER. I have routine that will insert a line in between other lines. So if the user wants to insert a new line 10 the below will run. I am curious as to why this works.
update ORDLINES
set LINE_NUMBER = LINE_NUMBER + 1
where ORDER_NUMBER=1000  and LINE_NUMBER >= 10

How does SQL number to go to the last line and increment that number and then work backwards? If it did not do that then there would be a PK duplicate record violation. Is there a better more proper way to accomplish this? In the sp I pass down the value of order and line numbers.
0
Comment
Question by:rwheeler23
  • 2
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 2000 total points
ID: 40015895
How does SQL number to go to the last line and increment that number and then work backwards? If it did not do that then there would be a PK duplicate record violation. Is there a better more proper way to accomplish this? In the sp I pass down the value of order and line numbers.


Looks good to me.

There isn't a duplicate PK record violation because a couple things:
1. you enter this SP with no duplicates, as constrained by your PK of Order_Number,Line_number.
2. you're incrementing all Line numbers 1, for all records which are line_number >= 10

this means that Order_number 1 has line numbers
1, 4, 7, 9,10,11,12,13,14

when you add 1 to every one over or equal to 10 you get:
1, 4, 7, 9,11,12,13,14,15



-Now, if you would subtract 1 rather than add 1 -or- add 1 where line_number is <= 10, you may very well throw PK constraint errors.
0
 

Author Comment

by:rwheeler23
ID: 40016333
Interesting. I would think it would try to update line 10 to line 11 and then bump into a duplicate. So it looks like it updates 15 to 16, 14 to 15, 13 to 14, 12 to 13, 11 to 12 and finally 10 to 11 leaving 10 available which I create at the very end.
0
 
LVL 6

Expert Comment

by:Dulton
ID: 40016402
Yeah, as long as it's all contained within the same DML statement and you do your insert afterward, the way you're going about it, I don't see any primary key violations.
0
 

Author Closing Comment

by:rwheeler23
ID: 40016450
Thanks for the insight!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question