JChilds Posted February 17, 2011 Share Posted February 17, 2011 I have a table with 35,000 rows. Row 1 has a cell with ID 1 Row 2 has a cell with ID 2 etc etc There are a heap of rows that should not be in there and I want to remove them. I want to delete any trace of them, so just deleting the row wouldn't work, as the ID's would skip a number. So I need to change all of the ID's 'up' one. Is there an easy way to do this? As I am totally stumped. Quote Link to comment Share on other sites More sharing options...
ronverdonk Posted February 17, 2011 Share Posted February 17, 2011 ALTER TABLE table_name DROP id; ALTER TABLE table_name ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1; Ronald Quote Link to comment Share on other sites More sharing options...
litebearer Posted February 17, 2011 Share Posted February 17, 2011 CAUTION!!!! If the table has relationship(s) with any other tables DO NOT RE-INDEX IT Quote Link to comment Share on other sites More sharing options...
JChilds Posted February 18, 2011 Author Share Posted February 18, 2011 Thanks for the reply ronverdonk. Sorry it has taken me this long to respond, work was crazy this morning. The problem is that this ID does infact correspond to another table in which I have to do the same thing, except ID is not unique there. File_id in table1 is unique File_id in table2 can have alot of items. Thanks for the help thus far Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted February 18, 2011 Share Posted February 18, 2011 CAUTION!!!! If the table has relationship(s) with any other tables DO NOT RE-INDEX IT Just to reiterate. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted February 18, 2011 Share Posted February 18, 2011 The range of an unsigned integer, which is the minimum you should be using for your ID column, is 0 to 4,294,967,295 Don't worry about some deleted IDs out of 35,000 rows. Your database doesn't care about this and neither should you or your code. Imagine the inefficiency and wasted time, if every time someone died, your government moved up everyone's assigned ID number Quote Link to comment Share on other sites More sharing options...
JChilds Posted February 18, 2011 Author Share Posted February 18, 2011 Its hard to explain why, but that is not an option here. There can be no missing numbers Quote Link to comment Share on other sites More sharing options...
JChilds Posted February 18, 2011 Author Share Posted February 18, 2011 The only thing I can think of is not pretty. - Get ID number - Delete that row - Minus one off every ID that is larger than that id Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted February 18, 2011 Share Posted February 18, 2011 Its hard to explain why... ^^^ That's a sure sign that you don't have a proper reason to do something that multiple people have told you is not done in database designs. Computers only do exactly what their programming tells them to do. If you don't have a definable and clearly stated reason for doing something, then you are wasting your time trying to do it because it has nothing to do with the problem you are trying to solve. Frankly, we see this all the time in the Forum, things being overcomplicated due to a lack of definition - OP: I'm trying to do the following (small part of the overall design) ______________. People trying to help: What you stated is not done for the following reasons ____________. Rather than telling us one small part of what you think you want to do, what overall goal are you trying to accomplish? OP: My overall goal is to do the following ______________. People trying to help: Why didn't you say that in the first place. What you stated you are trying to make work has nothing to do with achieving that goal. Here is the simple way you can do ______________. Quote Link to comment Share on other sites More sharing options...
JChilds Posted February 18, 2011 Author Share Posted February 18, 2011 ...ok I need to be able to remove jobs(id) without there being a trace of them, so even if someone wanted to they couldn't tell they were there. Quote Link to comment Share on other sites More sharing options...
JChilds Posted February 18, 2011 Author Share Posted February 18, 2011 This is what I will probably be using: Can anyone see any problems? DELETE from table1 WHERE id = $id UPDATE table1 SET id = id - 1 WHERE id > $id And the same for table2 Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 18, 2011 Share Posted February 18, 2011 Hi The key like that shouldn't be meaningful, so having one missing shouldn't really mean anything. You solution to remove them might work but will be dog slow. However depending on how the relationship is between the 2 tables you might have to update them row by row (ie, if it is a foreign key the database will object). All the best Keith Quote Link to comment Share on other sites More sharing options...
gizmola Posted February 18, 2011 Share Posted February 18, 2011 Is this something you're doing just to clean up the data one time, or something you plan to use on an ongoing basis? Quote Link to comment Share on other sites More sharing options...
JChilds Posted February 18, 2011 Author Share Posted February 18, 2011 It is going to be ongoing. I understand it is going to be slow. That is why I am here, to see if there is a better way of doing it. As a general rule, only ~ 1000 records will need to be updated(each time) as most of the 'problems' are the more recent jobs. Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 18, 2011 Share Posted February 18, 2011 Hi Is ID defined as a foreign key? If so you would need to copy the existing row to its new higher position, update all the rows on the 2nd table to point at the new row then delete the old existing row. There is no way to do it elegantly, and with a reasonably designed database there should be no need to do it. In situations like the the ID key should be meaningless. All the best Keith Quote Link to comment Share on other sites More sharing options...
JChilds Posted February 18, 2011 Author Share Posted February 18, 2011 Im not very well versed in mysql at all. id is int(11) There are no relationships defined in 'designer' in phpmyadmin Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 18, 2011 Share Posted February 18, 2011 Hi If no relationships then it is easier. Just be aware that if anything goes horribly wrong on the updates your data will possibly be a corrupted mess. Also make very sure that any other tables that refer to the ID are updated. Be aware than it that ID field is put out on a form that users use for updates then there is a good chance that anyone half way through an update when you change the data then they will land up updating the wrong record. Same for anyone who might have bookmarked the page with an ID in the page url. All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.