Reorder position in a sequence via Mysql

Asked

Viewed 295 times

1

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

Just to illustrate: the call will have some arrows, when you click on the arrow pointing upwards, this call will increase the number of the column sequencia, and alter all other records so that that call gets above what was above. Same thing if you click the down arrow, changing its order, remembering that the sequence column cannot have repeated numbers.

Can anyone help? I am 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

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

UPDATE chamado SET sequencia = sequencia - 1 WHERE cod = 1
UPDATE chamado SET sequencia = sequencia + 1 WHERE cod = 2
java mysql netbeans
  • 3

    There’s nothing ready, or it’s homework?

  • I have everything ready, asked to add this functionality in the Software, and I’m hitting myself how can I do it.

  • 3

    @Aronks your question is quite broad. I understood what has to be done, but exactly, what is your difficulty, or the problem?

  • 3

    Specify better, preferably what you have already tried. Asking for a 'zero' solution does not demonstrate any effort on your part to solve the problem..

  • @Aron, you’re using some framework?

  • 1

    I thought to take the record, and increment + 1 in it, and a FOR in the other records, to increment the top, and decrease the bottom, but our database is not very fast (it’s external), there would be an easier way to solve?

  • @Gustavocinque No, only using Netbeans.

  • Aron, I don’t have much experience in swing, but the idea would be, every time a record was qualified with "+1", the table would reload, and in the functions that model the table, there would be a separation by the field voto (for example). I also don’t know if it’s the best idea... Chamado implemented? If so, it would just create a new field within it.

  • Sorry, I had understood "create another table" instead of "column" up there. I advise using the framework Hibernate to be able to map the models more calmly, and without burning the head with SQLs, but I believe the project is already too advanced to do this.

  • 1

    [note to editor] @Earendul, check the guide How we should format questions and answers?, especially the part about the use of \``.

  • 1

    Aronks, click [Edit] and update the question, check [Ask] for more details. Like this: 1) Negative votes can be reversed. 2) You will receive answers instead of comments asking for clarification.

  • This question seems to be out of context because it’s about a specific problem that won’t help anyone else. Moreover the question is very broad and has the answer in the question itself.

Show 7 more comments

1 answer

4


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

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 qq position:

Store in a variable o cod and sequencia from the original call to rise 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

Browser other questions tagged java mysql netbeans

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