Dynadot Aftermarket: discover domain gems worth investing in. Visit our aftermarket.

Update ID in MySQL?

SpaceshipSpaceship
SpaceshipSpaceship
Namecheap AuctionsNamecheap Auctions
Watch

snike

Established Member
Impact
3
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.
 
Last edited:
0
Close
The views expressed on this page by users and staff are their own, not those of NamePros.
GoDaddyGoDaddy
MySQL can do this if you set the ID column to AUTO_INCREMENT PRIMARY KEY
 
0
Close
Yes, it is set like that. So what would the query look like?
 
0
Close
you don't need to set the ID if it's auto_increment just do a regular insert statement

Code:
INSERT INTO table (name) VALUES ('Blah');
 
0
Close
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.
 
0
Close
The following might work:

Code:
UPDATE table_name SET id=0 WHERE id=2
ALTER TABLE table_name AUTO_INCREMENT
 
0
Close
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 :) !)

Code:
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
 
Last edited:
0
Close
DomainEasy: white label portfolios are live. Enable yours.

We're social

Escrow.com
Spaceship
Domain Recover
CryptoExchange.com

NamePros updates

Search: choose where to search from the All tab
Open the search at the top of any page. On the All tab, the list next to the search box lets you choose where to search: everything, all...
AIVikings
Catchy
  • The sidebar remains visible by scrolling at a speed relative to the page’s height.
Back