Remove row and 'move up' "ID"

[JChilds]

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.

[ronverdonk]

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

[litebearer]

CAUTION!!!! If the table has relationship(s) with any other tables DO NOT RE-INDEX IT

[JChilds]

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

[Pikachu2000]

CAUTION!!!! If the table has relationship(s) with any other tables DO NOT RE-INDEX IT

 

Just to reiterate.

[PFMaBiSmAd]

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 

[JChilds]

Its hard to explain why, but that is not an option here.

There can be no missing numbers

[JChilds]

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

[PFMaBiSmAd]

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 ______________.

 

[JChilds]

...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.

[JChilds]

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

 

 

[kickstart]

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

[gizmola]

Is this something you're doing just to clean up the data one time, or something you plan to use on an ongoing basis?

[JChilds]

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.

[kickstart]

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

[JChilds]

Im not very well versed in mysql at all.

 

id is int(11)

There are no relationships defined in 'designer' in phpmyadmin

[kickstart]

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