I have one table in my database which has 3 columns named id , roll_no, name resp
- now as usual I have set the id column as primary key and auto increment
- and every time a record is inserting I am assigning roll number randomly using rand() function
- but now I want to set column roll number in a way , when a new record is inserting it will increment automatically starting from 1000 and so on
- but I am not able to do it , please guide me
Create another table where roll_no is autoincrement.
If you insert in your table, at first insert in another table and get last_insert_id. That will be your roll_no.
UPDATE If you have a lot of users for this table and would to hold the rule id1 < id 2 => roll_no1 < roll_no2, you should to pack both queries in stored procedure.
Quite simple. For an existing table to start incrementing at one thousand, run this query with whatever table name you are using.
ALTER TABLE users AUTO_INCREMENT=1000;
Here is an example to start with a clean table
CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000;
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.