I am using MySQL, I don't have a good way to do this.
I have a table with a position
field, which I need to keep track having values from 1 to 10,000.
Let's say I insert a record in the middle at 5000th position. So position 5000 to 10,000 need to be updated to the new position; old 5000 become 5001, 5002 becomes 5003...
Is there a good way to implement this without affecting so many records, when 1 single position is added?
Adding from the position 1st is the worst.
I'd rethink the database design. If you're going to be limited to on the order of 10K records then it's not too bad, but if this is going to increase without bound then you'll want to do something else. I'm not sure what you are doing but if you want a simple ordering (assuming you're not doing a lot of traversal) then you can have a
next_id column to indicate sibling relationships. Here's the answer to your questions though:
update some_table set some_position = some_position + 1 where some_position > 5000 and some_position < 10000
You can try the below approach : USE tempdb; GO CREATE TABLE dbo.Test ( ID int primary key clustered identity(1,1) , OrderNo int, CreatedDate datetime ); --Insert values for testing the approach INSERT INTO dbo.Test VALUES (1, GETUTCDATE()), (2, GETUTCDATE()), (3, GETUTCDATE()), (4, GETUTCDATE()), (5, GETUTCDATE()), (6, GETUTCDATE()); SELECT * FROM dbo.Test; INSERT INTO dbo.Test VALUES (3, GETUTCDATE()), (3, GETUTCDATE()); SELECT * FROM dbo.Test; --To accomplish correct order using ROW_NUMBER() SELECT ID, OrderNo, CreatedDate, ROW_NUMBER() OVER(ORDER BY OrderNo, ID) AS Rno FROM dbo.Test; --Again ordering change INSERT INTO dbo.Test VALUES (3, GETUTCDATE()), (4, GETUTCDATE()); SELECT ID, OrderNo, CreatedDate, ROW_NUMBER() OVER(ORDER BY OrderNo, ID) AS Rno FROM dbo.Test DROP TABLE dbo.Test;