Insert record into table with position without updating all the records position field

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 prev_id and 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;


  • Passing on non-standard evaluation arguments to the subset function
  • Encapsulating OpenGL functionality in C++ objects while using QOpenGLFunctions
  • Ruby 'require' error: cannot load such file
  • Matching first char in string to digit or non-standard character
  • Best way to determine if a user has viewed data
  • What should be the return value of a custom function addEdge in a new class based on BGL?
  • PHP and MySQL: Comparing month and day with dynamic year
  • AWS S3 implementation for serving private files
  • Intellij Idea Terminal shortcut not working
  • Android NFC read Tags issue. Activity starts each time on data received
  • Calling C function from lua
  • segue from landscape only to portrait only
  • Write output of for loop to multiple files
  • Plotting densities in R
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • How to make R's read_csv2() recognise the text characters properly
  • Can I programmatically choose the Android layout folder?
  • Android Activity.onWindowFocusChanged doesn't get called from within TabHost
  • TextToSpeech.setEngineByPackageName() triggers NullPointerException
  • C: Incompatible pointer type initializing
  • Assign variable to the value in HTML
  • Time complexity of a program which involves multiple variables
  • Magento Fatal error: Maximum execution error solution, on WAMP
  • Bad request using file_get_contents for PUT request in PHP
  • Breeze - Deleted Items nav properties bug
  • What is Eclipse's Declaration View used for?
  • javaw.exe and eclipse startup problems
  • recyclerView does not call the onBindViewHolder when scroll in the view
  • Controls, properties, events and timers running in design time
  • How to extract text from Word files using C#?
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • WOWZA + RTMP + HTML5 Playback?
  • Compare two NSDates in iPhone
  • Android Studio and gradle
  • SQL merge duplicate rows and join values that are different
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • need help with bizarre java.net.HttpURLConnection behavior
  • How to get NHibernate ISession to cache entity not retrieved by primary key