Invert id sequence in a table

Question

I had to migrate a site that was made in pure PHP for Wordpress, searched the internet and managed to copy the data from the table of the old site to the structure of Wordpress, which happened one thing, all the news were copied backwards, then when the news that are exhibiting first are the older ones, then from what I understood if I could reverse the order the ID in the table I solve this, I searched on the internet and found no example, of how to do an UPDATE in a table with sequential number.

In my Bank has a column called "ID" the comment record of 1 and goes up to 114 i wish that ID had the records reversed. EX: 114,113,112. until you reach number 1

Answer 1

I do not believe that reversing the Ids is the best solution, but as it was what you asked, follows:

• Back up your data if you have any problems with the following steps;

• check which is the largest ID of all of the desired table;

• multiply this value by 2, and add 1. Note for use in query.

  Ex: if the largest ID is 213, the final value will be 427 (after all, 213 2 + 1 = 427).

• Run this once only query, replacing the 427 of the example with the value found in the previous step:

  UPDATE tabela SET id = 427 - id;
  

Okay, you’ll have the Ids in reverse order. The only thing that can cause strangeness is that they will not start from 1, but this should not be a problem for the desired purpose.

The reason we use a higher value is quite simple: You cannot have two repeated Ids, so the above calculation result will necessarily always be higher than the ones in the table before the UPDATE, ensuring that the execution does not contain a repeated index error.

If you really want to keep the numbering of 1 (unnecessary, but in any case...) you can do this query after the previous (again, only once):

UPDATE tabela SET id = id - 213

This time by subtracting the value of the largest ID obtained at the beginning of the steps.

_{^{I used different values to keep your focus on the steps and not just on query final. The figures for the question are 229 and 114 respectively.}}

Answer 2

I believe it is correct the way it is... the display problem may be caused by another factor, configuration perhaps. If you reverse the sequence, the next post, would be code 115 and would be right after the "hello world" that was the first... recommend not perform this procedure without first checking what can be.

But if we look only at the question of reversing the numbering.

You first have to change all the numbers to an interval that has no conflicts. could do so:

update tabela set id = id + 1000;

Once done, you select by numbering the rows in descending id order and store them in a temporary table:

with temp as 
(
 select t.*, row_number() over (order by t.id desc) as i from tabela t order by t.id desc 
)

Finally, you apply the new id in the record, as the select row number stored in the temporary table:

update tabela set id = (select x.i from temp x where x.id = tabela.id );

Mysql 8 was used as an example. row_number() is only available from this version.

---

For other versions, the following code can be used:

update tabela set id = id + 1000;

update tabela set id = (select x.i from (select t.*,  @rownum := @rownum + 1 AS i from tabela t, (SELECT @rownum := 0) r order by t.id desc ) x where x.id = tabela.id );

I put in the Dbfiddle

The Bacco solution is clearly simpler and more functional for the case in question, but as I had already done the code to redo the numbering, I’ll leave it here too.