• Status: Solved
• Priority: Medium
• Security: Public
• Views: 306

# SQL Update lines in PK

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
rwheeler23
• 2
• 2
1 Solution

Commented:
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 Commented:
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

Commented:
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 Commented:
Thanks for the insight!
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.