How do I update all id fields with a single request?

Question

How do I update all id fields with a single request?

Let's say there is a table articles with the fields id,title,text.
id this is a field with auto-increment, but in this field the values are not in order, but in a spread, for example 0,1,5,6,20,22.
How can I order this field id with a single query, i.e. so that the values of the field id are ordered, (0,1,2,3,4,5...)?

P.S. in this case, we are not interested in links with this field id in other tables.
P.S. this is necessary for educational purposes.
By the way accidentally lost field title in the same table.
that is, this field was the name of the record, now there is the same number in all records table articles:
(entry 1: [id=0;title=2;])
(entry 2: [id=1;title=2;])
(entry 3: [id=5;title=2;])
How to change the field title with one request, in this case it does not matter what the value is, as long as it is different, for example title1,title2,title3.
I am interested in how you can change the field id in one query to order its values, and the second request to restore the field title, this is for training purposes, you need

0

mysql sql

Author: word, 2019-08-10

Source

2 answers

There is no such possibility, only in two stages.

ALTER TABLE `articles` DROP `id`;
ALTER TABLE `articles` ADD `id` INT(11) NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);

1

Author: , 2019-08-10 19:08:39

score 1 · Accepted Answer

The problem is that in the database there is no concept of "go in a row", in general. try this

SET @i:=0;
UPDATE articles SET id = @i:=(@i+1) WHERE 1=1;

update articles set title=concat('title', CAST(id as VARCHAR(50)));