Update ID in MySQL?

snike · 11-03-2009, 04:10 PM

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** · 11-03-2009, 05:18 PM

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

snike · 11-03-2009, 05:44 PM

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

**Eric** · 11-03-2009, 05:50 PM

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');

snike · 11-03-2009, 07:45 PM

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 · 11-04-2009, 04:26 AM

The following might work:

Code:

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

**enlytend** · 11-04-2009, 04:57 AM

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

!)
????: NamePros.com http://www.namepros.com/programming/620935-update-id-in-mysql.html

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

11-03-2009, 04:10 PM	THREAD STARTER #1 (permalink)
snike NamePros Regular Join Date: Mar 2006 Location: USA Posts: 497	Update ID in MySQL? 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 by snike; 11-03-2009 at 05:44 PM.

11-03-2009, 05:50 PM	#4 (permalink)
Eric Tech Support Join Date: Mar 2005 Posts: 4,944	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');

11-04-2009, 04:26 AM	#6 (permalink)
qbert220 NamePros Regular Join Date: Jul 2007 Location: UK Posts: 394	The following might work: Code: UPDATE table_name SET id=0 WHERE id=2 ALTER TABLE table_name AUTO_INCREMENT __________________ DNS Test and Check - PHP Tutorials Credit Score Ratings FHA Loan Information

11-04-2009, 04:57 AM	#7 (permalink)
enlytend Forum Moderator Join Date: Aug 2006 Location: USA Posts: 2,152 Follow @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 !) ????: NamePros.com http://www.namepros.com/programming/620935-update-id-in-mysql.html 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 __________________ Enlytend Solutions - Internet marketing and web development Was my advice helpful? Please consider a small donation to the National Canine Cancer Foundation - a tax deductible 501(c)(3)that directly funds cancer research Last edited by enlytend; 11-06-2009 at 05:52 AM.

11-03-2009, 05:18 PM	#2 (permalink)
Eric Tech Support Join Date: Mar 2005 Posts: 4,944	MySQL can do this if you set the ID column to AUTO_INCREMENT PRIMARY KEY

11-03-2009, 05:44 PM	THREAD STARTER #3 (permalink)
snike NamePros Regular Join Date: Mar 2006 Location: USA Posts: 497	Yes, it is set like that. So what would the query look like?

11-03-2009, 07:45 PM	THREAD STARTER #5 (permalink)
snike NamePros Regular Join Date: Mar 2006 Location: USA Posts: 497	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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)