Update table by Preference and reorder table accordingly-CodePudding

I have this app when you drag and drop rows in a list it reorders and SETS that row by preference number, 1-5 for example, 1 being most priority. So if I have 5 records in the list, I can then drag each row and when dropped it will reorder the list by preference. I can move row 5 to row 1, or row 2 to row 3, etc... This will update the preference number in the SQL table according where you drop.

This app is in real-time. When new rows are added to the table automatically, they have an initial preference of "0". This query will add the next number preference to the record, so if I have rows with preferences of 1-5, a new record comes in, then it's assigned a preference of 6:

with CTE as (
    select Id, Preference, cp.maxpref, row_number() over(order by Id) rn
    from [RadioQDB].[dbo].[Rad5]
    cross apply (
        select max(preference) maxpref
        from [RadioQDB].[dbo].[Rad5] p
    ) cp
    where preference = 0
)
update cte
set preference = maxpref   rn
where preference = 0

The issue I am having now is if a record is removed from the list during an update (not user drag and drop), let's say you have 1,2,3,4,5 records in the list. If during the table update, a record is removed automatically, let's say #2, then you end up with preferences 1,3,4,5. How can I move up everything and reorder the table accordingly by preference? 1 stays the same, 3 moves to 2, 4 moves to 3 and so forth.

Thank you.

CodePudding user response：

To insert a new record with Preference=Max(Preference) 1 use the following query:

insert into Rad5 values(10,(select max(Preference) 1 from Rad5));
-- inserts a new record with id=10

To reorder the records according to the Preference after deleting a record try the following:

with cte as (
select id, Preference, row_number() over (order by Preference) as rn
from Rad5)
update cte set Preference=rn;

You can use Trigger on delete from your table to call the update query automatically whenever a record is deleted, if you want to do so use the following:

create trigger Rad5_Delete on Rad5
for delete 
as
with cte as (
select id, Preference, row_number() over (order by Preference) as rn
from Rad5)
update cte set Preference=rn;

See a demo from db<>fiddle.