...

View Full Version : recreating and replacing database column values



chump2877
06-27-2005, 01:42 AM
Here's my question:

If I have a MySQL database column that reads:


index_no

1

2

3

4

5

and I delete one of the rows, let's say the one where index_no='2'....so now the column reads:


index_no

1

3

4

5

How do I recreate and replace the existing column values so that the column reads:


index_no

1

2

3

4

????

This database is ****, so I don;t have any other columns to cross-reference this column with....is there a way to simply recreate and replace the column values so they start at '1' (at the top) and (in increments of 1) go on to infinity (without cross-referencing any other columns, hopefully)?

Thanks again.

SeeIT Solutions
06-27-2005, 02:02 AM
This is one way to do it, I don't know if there is a more efficient way but this works.


$query = "ALTER TABLE `table` DROP `index_no`;"; //delete id column
mysql_query($query); //run the query
$query2 = "ALTER TABLE `table` ADD `index_no` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;"; //add id column
mysql_query($query2); //run the query

chump2877
06-27-2005, 04:40 AM
Efficient enough for me, plus it works...thanks!

Also, methinks my SQL is not so good.....Do you mind explaining to me how this query works:


ALTER TABLE `table` ADD `index_no` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

thanks again... :thumbsup:

SeeIT Solutions
06-27-2005, 07:35 AM
"ALTER TABLE `table`" //edit the table's structure (like SELECT but for editing)
"ADD `index_no`" //edit it by adding this column name
"INT" //column type (integer)
"NOT NULL" //doesn't allow it to be NULL (must have a value)
"AUTO_INCREMENT" //makes it an auto incrementing field (so when you add a column it makes it 1 higher than last entry
"PRIMARY KEY" //makes it the primary key for this table
"FIRST" //column position (so it's the first column because ids usually are

So basically the code i gave u gets rid of the column then places it back in and since it is an auto increment column it inserts new ids starting at 1 and incrementing by 1 each time.

chump2877
06-27-2005, 07:52 PM
Thanks, good to know what all that means.... :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum