MySQL Update Sort Order Column Using Counter

Question

I have a table which has rows inserted and removed which creates problems with the Sort Order. Sometimes I'm finding duplicate SortOrder values such as:

1 2 2 3 4 4

I reset the SortOrder values - I just can't seem to figure out the right query.

e.g.

Table1 contains columns: ssID, showID, sName, sDisplay, SortOrder

Primary Key is ssID

When new data gets inserted/removed I need to reset the SortOrder

New data is being inserted using a number of different procedures hence there's a need to clean the table up.

I can get the SortOrder values I want with this:

SET @t1=0;
SELECT *, 
    @t1 := @t1+1 As counter 
FROM Table1  
WHERE showID = 123 
ORDER BY SortOrder ASC

From this, 'counter' is populated with 1, 2, 3, 4, 5 etc but I can't work out how to UPDATE the results from the SELECT to replace SortOrder with each new value from counter

update your question and add info for the primary key of the table Table1 .. and add the sql update code you are trying .. — ScaisEdge
– ScaisEdge, Commented May 1, 2020 at 6:57

ScaisEdge · Accepted Answer · 2020-05-01 07:00:38Z

3

Assuming the table1 prumary key is the column your_table1_primary_key

You coudl try using

SET @t1=0;

UPDATE Table1
INNER JOIN (
    SELECT your_table1_primary_key, @t1 := @t1+1 As counter 
    FROM Table1 
    WHERE showID = 123 
    ORDER BY SortOrder ASC
) t on t.your_table1_primary_key = Table1.your_table1_primary_key
SET Table1.SortOrder =  t.counter

answered May 1, 2020 at 7:00

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Update Sort Order Column Using Counter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related