how to update id as row number in MySQL 5.7



  • I have a table did not contains id, now I add the id column and want to update the id as row number, what should I do? this is my table DDL:

    CREATE TABLE `spark_car_brand` (
      `alpha` varchar(255) DEFAULT NULL,
      `id` int(11) DEFAULT NULL,
      `brand_id` varchar(255) DEFAULT NULL,
      `brand_name` varchar(255) DEFAULT NULL,
      `brand_logo` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    I am using this SQL seems did not work:

    update spark_car_brand
    set id = ROW_NUMBER();
    


  • If you change the id column to AUTO_INCREMENT NOT NULL it will again a unique identifier. AUTO_INCREMENT columns need to be part of a primary or unique key and since you don't have one, this is recommended:

    alter table spark_car_brand
    modify id int not null auto_increment primary key;
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2