Hi All
I have a scenario, that in my member table am storing the group_id,member_id,leader_number.
for example there are 15 members in group-1 the leader number ,vl be assigned 1,2,3...15 to the members of that group.. when ever any member or members r deleted, again I have to reorder the leader number sequentially..
when the leader number is deleted , for example 1,2,3,4,5,6...when 3 is deleted 4 should come to 3's position vise versa...can any one help me in solving this
UPDATE T
SET leader_number = new_no
FROM (
SELECT *,
new_no = row_number() OVER (
PARTITION BY group_id ORDER BY leader_number
)
FROM member
) T
WHERE leader_number <> new_no