Re-ordering rows based on position SQL Server

Question

I have seen many questions on SO regarding this question but none is relevant to my scenario. I am nowhere near an SQL guy apart from doing basic CRUD operations. Hence I am quite stuck with this.

I have a table.

myTable [rID, newsID, OrderPosition] where;

rID is primaryKey int column, 
newsID int is the ID of an item from another table and,
OrderPosition int to hold the position of the rows. 

myTable will always have a total of 10 rows.

So, initially, lets assume myTable has the following data:

rID    newsID    OrderPosition
100    4000      1
101    4100      2
102    4200      3
103    4300      4
104    4400      5
105    4500      6
106    4600      7
107    4700      8
108    4800      9
109    4900      10

The expected functionality should be as follows;

INSERT NEW

when inserting a new item, user should be able to insert it into any position he/she desires. Right now, I only managed to insert the new record to the first position by deleting the OrderPosition = 10 row, assigning the new record OrderPosition of 0, and reorder the table. But client wants to select which position the item should go. In which case I assume, the OrderPosition = 10 will be deleted again?

DELETE

When a record from this table is deleted, since there will always be a total of 10 records, I need to get the latest entered record from another table [tblNews] and insert it to the 10th position (I can get the last record from tblNews by ordering descending by the date it was entered.) Since I don't know which record they will delete, I don't know how to re-order the table after a record has been deleted.

Any help, code, direction to an article would be very much appreciated.

=========== EDIT ====================

The UPDATE method mentioned in the answers will not work for me since; e.g. user wants to insert a new record into the 5th order position. This would mean, the order position 10 would be deleted and the current records with order postions 5,6,7,8 and 9 is to be incremented by one

Answer 1

Something like this will work for you, I guess:

CREATE PROC uspMyTableInsert
(
    @newsID INT, @order int
)
AS
BEGIN

    UPDATE MyTable
    SET OrderPosition = OrderPosition + 1
    WHERE OrderPosition >= @Order;

    INSERT INTO MyTable VALUES (@newsID, @order);

    DELETE FROM dbo.myTable WHERE OrderPosition = 11


END

So, for inserting you have 3 steps:

First you update orders of items to follow (+1), then insert your item, and at the end delete 11th row.

Similar for Delete - also 3 steps, but first you delete the row, then update orders of following rows (-1 this time) and at the end just insert your new 10th row.

CREATE PROC uspMyTableDelete
(
    @order int
)
AS
BEGIN

    DELETE FROM dbo.myTable WHERE OrderPosition =@order

    UPDATE MyTable
    SET OrderPosition = OrderPosition -1
    WHERE OrderPosition > @Order;

    INSERT INTO MyTable 
    SELECT TOP 1 newsID, 10 
    FROM tblNews ORDER BY newsID DESC


END

SQLFiddle DEMO

Answer 2

When inserting the row, you need to "move" the rows below to make the room for it. For example, inserting on a position 4 could be done like this:

DELETE FROM myTable WHERE OrderPosition = 10; -- If you want the keep the table from growing beyond 10 rows.
UPDATE myTable SET OrderPosition = OrderPosition + 1 WHERE OrderPosition >= 4;
INSERT INTO myTable VALUES (..., 4);

Conversely, after you delete the row, you can move the rows "below" it back up:

DELETE FROM myTable WHERE OrderPosition = 4;
UPDATE myTable SET OrderPosition = OrderPosition - 1 WHERE OrderPosition > 4;
-- Now you can insert the row on position 10 if desired.

Note that even if OrderPosition has a UNIQUE constraint on it, UPDATE won't violate it.

[SQL Fiddle]

Answer 3

when inserting a new item, user should be able to insert it into any position he/she desires. Right now, I only managed to insert the new record to the first position by deleting the OrderPosition = 10

The order of data when returned from database is determined by the sql query - I assume the sql query ayou are referring to has "order by OderPosition".

You can insert,update any record any way you want.

update mytable set newsID = "newid" where orderposition=10

this doesn;t make sense:

When a record from this table is deleted, since there will always be a total of 10 records

Answer 4

Not really sure what the problem is but will try to help.

First if the ColumnPosition is to have only 10 different numbers it should have the constraint unique on it (to avoid software mistakes).

Now say you want to do this for position five, it would have to look like: delete from myTable where ColumnPosition = 5; insert into myTable (newsID, OrderPosition) values (val, 5);

This should occur within a transaction and it's assuming your rID is a auto-incremented ID.