[SQL] SQL to update rows with reordered priority ID in order after deleting few records...

sample data/table is in the below SQL playground.

https://sqlfiddle.com/sql-server/online-compiler?id=ecc05c23-cfa4-4546-9334-5286b88eb2a3

screenshot of the table below enter image description here



Scenario- In this table there are 2 template Ids and for each template Ids there are 3 rule records. For each template Ids, the associated rules have priority in ascending order. Using below query , I fetch template Ids, where any of the rules' creation date passed 1 week and I get the below template Ids

SELECT template_id,* FROM TestRule where create_date < DATEADD(day, -7, getdate())


I get rule Id, 2 and 5 and I delete it.

After deletion, the priority of the rules will be like below

rule id, template id, priority
1 100 1
3 100 3
4 200 1
6 200 3


I want to reset the priorities based on the current order so that the priorities will be 1 and 2 instead of 1 and 3 for each template. I can do that by fetching data in the backend service and update priority but looking for a solution in SQL for better efficiency. I did some research in the net to solve the issue but could not find similar problem addressed . I use sql server

Continue reading...