Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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
Asked:
rwheeler23
  • 2
  • 2
1 Solution
 
DultonCommented:
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
 
rwheeler23Author 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
 
DultonCommented:
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
 
rwheeler23Author Commented:
Thanks for the insight!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now