Solved

Reindex MySql table

Posted on 2011-09-07
6
671 Views
Last Modified: 2012-05-12
I have a MySql table with an auto-increment id field, and in the table I have 4 records that do not change and have ids 1 to 10. The table also contain another 20+ records with id's eg 11-31. The last 20 records will be regularly deleted and re-iniserted with slightly different data, is there anyway of resetting the autoincrement back to the last record id+1, using Php without creating a new table?
0
Comment
Question by:ncw
  • 2
  • 2
  • 2
6 Comments
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 500 total points
Comment Utility
0
 
LVL 1

Author Comment

by:ncw
Comment Utility
This option from your link could be usefull:

ALTER TABLE tbl AUTO_INCREMENT = 100;
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
Comment Utility
Should reset the autoincrement

    $q = "alter table test AUTO_INCREMENT = 2";
    $r = mysql_query($q);
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
Deleted and reinserted with slightly different data?  So why not just update the rows instead of all that trouble?
0
 
LVL 1

Author Comment

by:ncw
Comment Utility
Deleting and reinserting data causes the id to start at the next auto-incremented id unless you recreate the table or alter the auto-increment start value, as far as I know.
0
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
Deleting and reinserting data causes the id to start at the next auto-incremented id unless you recreate the table or alter the auto-increment start value, as far as I know.

Exactly.  My point is, why delete and reinsert at all when you can just update the rows to reflect the slightly different data?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now