2

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.

CharlesB
  • 86,532
  • 28
  • 194
  • 218
sebas23
  • 1,463
  • 2
  • 10
  • 10

2 Answers2

5

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
Abdullah Jibaly
  • 53,220
  • 42
  • 124
  • 197
  • I liked the prev_id , next_id design for such order management. Thanks @Abdullah – Maulik Vora May 15 '12 at 05:28
  • 1
    @AbdullahJibaly just curious, with next/prev id, how does that work when it comes to selecting the data, order by what ??? – bumperbox May 15 '12 at 05:53
  • @Abdullah-Jibaly Thanks. I like the next/prev id too, same question, how do we select the data? – sebas23 May 15 '12 at 06:33
  • @Maulik Vora I found some answers here. http://stackoverflow.com/questions/675117/fetching-linked-list-in-mysql-database – sebas23 May 16 '12 at 01:41
0
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;
Karthik Kola
  • 93
  • 5