SQL Insert Lines In Between
Posted on 2015-01-09
What is the proper technique to insert lines into a SQL table using the primary key? As an example, let's say I have I have an order entry table. The PK is ORDRNMBR,LINENMBR. The user types in 20 lines on an order and then realizes he/she forgot to enter line 4. So what you need to do is:
OrderTable.ORDRNMBR,OrderTable.LINENMBR lines 4-20 need to become 5-21 freeing up line 4 so it can be inserted. How do you structure your update statement so it will start at line 20 and come down to line 4? If you were to start at line 4 and update upward you would run into duplicate line numbers.