i deleted 2000 row in my table and then i inserted same 2000 records but their index(id auto increment field) starting from 2001, now i want to update those index 2001 - 4000 to 1-2000
Asked
Active
Viewed 3,738 times
3
-
a simple update won't do the work? – sagi Jan 11 '16 at 09:48
-
1Are you using MySQL or Postgresql? Don't tag products not involved... – jarlh Jan 11 '16 at 09:53
-
1Why do you want to renumber? Those id's doesn't mean anything to you anyway. – jarlh Jan 11 '16 at 09:54
-
reset the sequence. som google research will deliver you the answer. – Remigius Stalder Jan 11 '16 at 10:28
-
i've dont that but reset sequence didn't work for existing data(ids) @RemigiusStalder – DexJ Jan 12 '16 at 14:57
-
because here two tables are compared in bases of their ids(unfortunately) so if by mistake one table's id get override we lose the track... @jarlh – DexJ Jan 12 '16 at 14:59
1 Answers
6
To update your id run the following command.
UPDATE table SET id = id - 2000;
This will update the id of records in your table and then you need update the table's sequence
ALTER SEQUENCE table_id_seq RESTART WITH 2001;
This will allow you to insert the data with the id 2001
Another way, delete all the records from the table and update the sequence of the table and reinsert the data.
Hope this will be helpful.
Harry Suren
- 676
- 1
- 12
- 23
-
2thanks for the answer, but it is not working for the record referenced from other table!! do you have any idea about?!! – DexJ Jan 12 '16 at 14:53
-
1@user3321823, you can use `ON UPDATE CASCADE` on the foreign-key of referenced table. This will update the reference whenever you update the referenced table as well. Reference : [Postgresql](http://www.postgresql.org/docs/current/static/sql-createtable.html) and [Stackoverflow](http://stackoverflow.com/a/1481524) – Harry Suren Jan 13 '16 at 05:15