CODEWITHSUNDEEP
mysql
rahularyansharma
rahularyansharma

Reputation: 10765

How to add auto increment column in existing table in mysql which starts from 1000 and increment by 1

I have a table which already have data . I want to add new column in that table as primary key which should start value from 1000 and should be auto increment as well.

I tried following sql commands

**

ALTER TABLE TABLE_NAME  AUTO_INCREMENT = 1000;
ALTER TABLE TABLE_NAME ADD COLUMN RowId  INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;

**

But still its shows that new column consists value from 1. I want that first record should start with 1000 and continue so on.

Upvotes: 0

Views: 1414

Answers (2)

P.Salmon
P.Salmon

Reputation: 17640

You might just want to do this in one statement.

+-------+---------+--------+---------------------+
| subid | clickid | status | datetime            |
+-------+---------+--------+---------------------+
|     1 |     123 | low    | 2018-07-24 20:20:44 |
|     2 |     123 | act    | 2018-07-24 21:20:44 |
|     3 |     231 | act    | 2018-07-25 20:20:44 |
|     4 |     231 | low    | 2018-07-25 21:20:44 |
|     5 |     789 | low    | 2018-07-26 20:20:44 |
|     6 |     789 | act    | 2018-07-26 21:20:44 |
+-------+---------+--------+---------------------+
6 rows in set (0.00 sec)

ALTER TABLE T ADD COLUMN RowId  INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST,
AUTO_INCREMENT = 1000;

+-------+-------+---------+--------+---------------------+
| RowId | subid | clickid | status | datetime            |
+-------+-------+---------+--------+---------------------+
|  1000 |     1 |     123 | low    | 2018-07-24 20:20:44 |
|  1001 |     2 |     123 | act    | 2018-07-24 21:20:44 |
|  1002 |     3 |     231 | act    | 2018-07-25 20:20:44 |
|  1003 |     4 |     231 | low    | 2018-07-25 21:20:44 |
|  1004 |     5 |     789 | low    | 2018-07-26 20:20:44 |
|  1005 |     6 |     789 | act    | 2018-07-26 21:20:44 |
+-------+-------+---------+--------+---------------------+
6 rows in set (0.00 sec)

show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `RowId` int(11) NOT NULL AUTO_INCREMENT,
  `subid` int(11) DEFAULT NULL,
  `clickid` int(11) DEFAULT NULL,
  `status` varchar(3) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`RowId`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=latin1 |

Upvotes: 2

Anton
Anton

Reputation: 1057

According to manual (https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html) you can change number for auto_increment simply inserting a value of 1000

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value

Upvotes: 0

Related Questions