Reset the priority of a record in the table

Asked

Viewed 193 times

1

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

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 demands. At this point we come to the problem. How to reorder demands for priority? For example, I could have demand 4 as a higher 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 hint?

UPDATE 1 - Better definition of the problem

The problem is at the time of updating. The user can, within the system, set the demand priority. Imagine that he is editing demand number 4 which 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 not just the update, you need 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 one would be that the demand with priority 1 change to the 2. The demand with priority 2, change to the 3 and etc... until arriving in 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
  • 1

    I didn’t understand the problem. It wouldn’t just be your query having a order by prioridade??

  • Do you want to sort the display by priority, or do you want to change the priorities with some other reference ? Post an example of what the ending would look like...

  • Oops. I changed the question to make it clearer.

  • the priority must be unique then

  • supondo
tabela (PK,DEMANDA,PRIORIDADE)
criar uma procedure do tipo altera_demanda (pdemanda,ppriode,pprioate) , 
faz um update da demanda para pprioate e depois um cursor alterando toda
as demenadas diferentes de pdemanda e maiores que pprioate somando 1
to the priority , I’m a little tied up here to write the SP but will the basic idea the apps would call this sp

1 answer

1


If you don’t have one UNIQUE in the priority column, you can do two update commands, but you will have to test before you are increasing or decreasing the registry priority:

If you’re lowering the demand priority:

--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;

If you’re raising the demand priority:

--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;

Dice:

inserir a descrição da imagem aqui

Result (1st Update):

inserir a descrição da imagem aqui

Result (2nd Update):

inserir a descrição da imagem aqui

I put in the Sqlfiddle

In case you have the UNIQUE, update the demand (before the update 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.

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.