Reset the priority of a record in the table

Question

Reset the priority of a record in the table

Navigation

#1 by (1 votes)

1

I need to do an update to reorder records in a table in an Oracle 9 bank. This table stores records that are requests made by users to TI. So, briefly, I have the following fields in the table:

PK | NOME DEMANDA | PRIORIDADE
------------------------------
1  | DEMANDA 1    | 1
2  | DEMANDA 2    | 2
3  | DEMANDA 3    | 3
4  | DEMANDA 4    | 4
5  | DEMANDA 5    | 5

The priority is who determines which project will be done first by IT. For business reasons, it may be necessary to change the priority of the demands. At this point we come to the problem. How to reorder demands for priority? For example, I could have Demand 4 as a priority, and the table would look like this:

PK | NOME DEMANDA | PRIORIDADE
------------------------------
1  | DEMANDA 4    | 1
2  | DEMANDA 1    | 2
3  | DEMANDA 2    | 3
4  | DEMANDA 3    | 4
5  | DEMANDA 5    | 5

Any tips?

UPDATE 1 - Improved problem definition

The problem is at the moment of the update. The user can, within the system, set the priority of the demand. Imagine that he is editing demand number 4 that has priority 4. He can change the priority to 1. So, basically the update would be something like: update table set priority = 1 where priority = 4. But update is not enough, you have to reorder all other demands.

If I simply do the update I am saying that the demand that had priority 1 now has priority 4. The correct would be that the demand with priority 1 changes to 2. The demand with priority 2, change to 3 and etc. ... until you reach demand 4.

The end result would be:

PK | NOME DEMANDA | PRIORIDADE
------------------------------
1  | DEMANDA 4    | 1
2  | DEMANDA 1    | 2
3  | DEMANDA 2    | 3
4  | DEMANDA 3    | 4
5  | DEMANDA 5    | 5

sql oracle

asked by anonymous 05.04.2018 / 15:35

1 answer

How to center text in a MessageBox? How to insert a new order via the Jasmin API (POST Order)?

score 1 · Accepted Answer

If you do not have UNIQUE in the priority column, you can do with two update commands, however you will have to test before it is increasing or decreasing the registry priority:

Whether Demand Priority is Decreasing:

--vamos alterar a demanda 4, para prioridade 1:
--1 é a nova prioridade, 4 é a prioridade anterior
update demandas set prioridade = prioridade + 1 where prioridade >= 1 and prioridade < 4;

--altera a prioridade que você quer:
update demandas set prioridade = 1 where pk = 4;

Whether the demand priority is increasing:

--agora alterando a demanda 5 para prioridade 6:
--6 é a nova prioridade, 4 é a prioridade anterior (era 3 e foi alterada no sql anterior)
update demandas set prioridade = prioridade - 1 where prioridade > 4 and prioridade <= 6;

--altera a prioridade que você quer:
update demandas set prioridade = 6 where pk = 5;

Data:

Result(1stUpdate):

Result(2ndUpdate):

Iputitin SQLFiddle

If you have UNIQUE , make an update on the demand (before the update that I put) that will be changed by playing a value that does not exist in the table. In this case, you would use 3 updates.