Clash Royale CLAN TAG#URR8PPP

How to reset value of a column by sequential values start by 1 with where condition

I have a table, which include 3 columns:

id, priority, dict_id

For each dict_id, it has maximum 1000 priority, start from 1 (sequential).
When I delete some priority, how to make the value of priority column sequential (not use trigger).
For example:
The original data

id, priority, dict_id
1, 1, 1
2, 2, 1
3, 3, 1
4, 4, 1
5, 5, 1
6, 6, 1


After delete id 3 and 5, the data of table:

id, priority, dict_id
1, 1, 1
2, 2, 1
4, 4, 1
6, 6, 1


I want the priority column like this

id, priority, dict_id
1, 1, 1
2, 2, 1
4, 3, 1
6, 4, 1


2 Answers
2

You may use an UPDATE FROM syntax

UPDATE FROM

UPDATE yourtab a
SET priority = s.priority
FROM (SELECT id,
row_number()
OVER (
ORDER BY id) priority
FROM yourtab) s
WHERE a.id = s.id;


Demo

I wouldn't recommend doing this, because the priority column with the ordering you want can easily be generated at the time you query using ROW_NUMBER, e.g.

priority

ROW_NUMBER

SELECT
id,
ROW_NUMBER() OVER (ORDER BY id) priority,
dict_id
FROM your_table;


You might want to avoid paying a frequent DML penalty everytime you add/remove records from your table.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.