Reorder position in a sequence via MySQL

1

I have a table of Chamados . I have to add an entire column that will be called sequencia and it will be used to search the order that the user wants.

Just to illustrate: the call will have arrows, when you click the arrow pointing up, this call will increase the number of the column sequencia , and change all other records so that the call is above what was above. Same thing if you click the down arrow, changing its order, remembering that the sequence column can not have repeated numbers.

Can anyone help? I'm developing in JAVA and MySQL .

EDIT:

Based on the comments, I decided to do so:

UPDATE chamado SET sequencia = sequencia + 1 WHERE cod = 1
UPDATE chamado SET sequencia = sequencia - 1 WHERE cod = 2

So it increases the value of the sequence where the user clicked, in case the code 1, and decreases the value of the code above so that code 1 has a value more auto than code 2, to do the opposite: / p>

UPDATE chamado SET sequencia = sequencia - 1 WHERE cod = 1
UPDATE chamado SET sequencia = sequencia + 1 WHERE cod = 2
    
asked by anonymous 05.09.2014 / 16:07

1 answer

4

In addition to its solution to reverse the position of two items, this is:

UPDATE chamado SET sequencia = sequencia + 1 WHERE cod = 1
UPDATE chamado SET sequencia = sequencia - 1 WHERE cod = 2

You can have one like this to change the item arbitrarily to what position:

Save in a variable the cod and sequencia of the original call to upload positions:

UPDATE chamado SET sequencia = sequencia + 1 WHERE sequencia >= destino AND sequencia <= original
UPDATE chamado SET sequencia = destino WHERE cod = codigosendomexido

And to descend positions:

UPDATE chamado SET sequencia = sequencia - 1 WHERE sequencia <= destino AND sequencia >= original
UPDATE chamado SET sequencia = destino WHERE cod = codigosendomexido
    
05.09.2014 / 18:04