469,159 Members | 1,520 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,159 developers. It's quick & easy.

How to get the same index number for the primary key after i truncate and upload data

111 100+
Hii all,


I have a project where before i upload the data from 1st database to 2nd database, im truncating the 2nd database and then trying to upload...

In this process, wat i found was that: My records in the database are something like:

`tbl_workers` (`wo_worker_id_pk` bigint(11) unsigned NOT NULL auto_increment,
`wo_worker_code` varchar(6) NOT NULL default '',
`wo_worker_catagory` varchar(50) default NULL,

PRIMARY KEY (`wo_worker_id_pk`),
UNIQUE KEY `wo_worker_code_idx` (`wo_worker_code`)
)

Is there any method to get the same index number for the primary key after i truncate and upload the records to the database..

For ex:
before truncating i find my database started with: 3000(wo_worker_id_pk)

ive got about 600 records

Now after truncating n uploading i find them starting with other number (3600)(wo_worker_id_pk)


so it is auto incrementing ......
I want them to start with 3000....

is there any way ....


plz help..
thnx.
hepsi...
Jul 19 '07 #1
2 1753
mwasif
801 Expert 512MB
Use this query to decrement the current auto_increment field to previous level. I have used 600, use as appropriate.
Expand|Select|Wrap|Line Numbers
  1. UPDATE tbl_workers SET wo_worker_id_pk=wo_worker_id_pk-600;
This query will set the autoincrement index to start from 3600 (next record will be added with this auto_increment index). Make sure this is the correct value.
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE tbl_workers AUTO_INCREMENT=3600;
If you dont want to do this, simply truncate the table and run the following query and insert the records again.
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE tbl_workers AUTO_INCREMENT=3000;
Why you want the auto_increment value to 3000?
Jul 19 '07 #2
rhepsi
111 100+
Use this query to decrement the current auto_increment field to previous level. I have used 600, use as appropriate.
Expand|Select|Wrap|Line Numbers
  1. UPDATE tbl_workers SET wo_worker_id_pk=wo_worker_id_pk-600;
This query will set the autoincrement index to start from 3600 (next record will be added with this auto_increment index). Make sure this is the correct value.
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE tbl_workers AUTO_INCREMENT=3600;
If you dont want to do this, simply truncate the table and run the following query and insert the records again.
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE tbl_workers AUTO_INCREMENT=3000;
Why you want the auto_increment value to 3000?
hiii,

thnx...

it helped me...


regards
hepsi....
Jul 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

25 posts views Thread by sql_server_2000_user | last post: by
6 posts views Thread by christopher.secord | last post: by
8 posts views Thread by paii, Ron | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.