Update ID in MySQL?

[snike]

Let's say I have this column in a database:

ID | Name
2 | Snike

Is it possible to have a php script update the ID number to the next available ID number? If in the example above there were 2 more column and the largest ID being 4, the script would update the ID from 2 to 5.

Can I do this? Thanks in advance.

 

[Eric]

MySQL can do this if you set the ID column to AUTO_INCREMENT PRIMARY KEY

 

[snike]

Yes, it is set like that. So what would the query look like?

 

[Eric]

you don't need to set the ID if it's auto_increment just do a regular insert statement

INSERT INTO table (name) VALUES ('Blah');

 

[snike]

But I don't want to add data into the database. I want to change the id of a column. from like 2 to whatever is next.

 

[qbert220]

The following might work:

UPDATE table_name SET id=0 WHERE id=2
ALTER TABLE table_name AUTO_INCREMENT

 

[enlytend]

You should be able to do this as a subquery (mysql 5.0 and higher) but you have to do some extra work because you can't use a subquery on the same table to provide a value to an update statement:

Something like this might work ... ( WARNING: not tested and since I rarely write complex sql statements any more there's a good chance it's not quite right or there's something stupidly easy that I'm not thinking of !)

Update table set id = (select * from (select (max(id) + 1) from table group by id) as i)  WHERE id = 2;

The additional "select" should store the value in a temporary table - since that's processed before beginning to execute the update, it should be OK.

Or here's a really easy alternative:

Get all the values from the old record (except ID)
Insert as a new record
Delete the record with the old ID