mysql middle row deleted and fix primary key with arranging order

Question

i have table with id that is primary key activated with 20 data inserted . and i have deleted row 15,16,17 and how can i arrange increasing order from 1 to 17

   CREATE TABLE `cart` (
    `id` int(255) NOT NULL,
    `productname` varchar(255) NOT NULL,
    `productquantity` varchar(255) NOT NULL,
    `productsize` varchar(255) NOT NULL,
    `productprice` varchar(255) NOT NULL

)

You need to change the value of the auto_increment for your column. See https://stackoverflow.com/questions/970597/change-auto-increment-starting-number — Prim, Oct 18 '18 at 09:13
Do not change the value of the primary keys. There is no need to do this (other than esthetics). Primary keys are used for other things -- such as foreign key relationships -- and changing them is generally a bad idea. — Gordon Linoff, Oct 18 '18 at 10:32

Madhur Bhaiya · Answer 1 · 2018-10-18T10:21:06.583

Determine row_number for each row, in an increasing order of id, starting from 1 in a Derived Table.
Join this Derived table with your main table, and update the id value to be equal to row_number. This will reset all the id values to start from 1 (with no gaps).

Try (works for all MySQL versions):

UPDATE your_table AS t1 
JOIN  
(
  SELECT @row_no := @row_no + 1 AS row_num, 
         id  
  FROM your_table
  JOIN (SELECT @row_no := 0) AS init 
  ORDER BY id 
) AS dt ON dt.id = t1.id  
SET t1.id = dt.row_num;

mysql middle row deleted and fix primary key with arranging order

1 Answers1

DB Fiddle DEMO