[SOLVED] how to fill missing index rows?

[mameha]

I have data as follows:

 

id | name

0 | john

1 | bob

3 | dave

4 | ralph

5 | david

7 | sam

9 | james

 

As you can see, the 'id' column has many missing values.

 

I want to autofill all the missing values so data becomes like this:

 

 

id | name

0 | john

1 | bob

2 | NULL

3 | dave

4 | ralph

5 | david

6 | NULL

7 | sam

8 | NULL

9 | james

 

Is there some command that can easily do this in SQL?  (MySQL5)

[trq]

The big question is why? Is id an auto incrementing field?

[mameha]

Its a little tricky to explain.

 

I am importing about 5000 records to another system.

 

The new system will create new keys for each record, therefore 'dave' above will become id=2 on the new system unless I put in a blank record for id2. 

 

I need to avoid this happening because people will expect the same id numbers for the records.

[mameha]

I suppose I am looking for some command in MySQL that does this:

 

$i=0;

while ($i<6000){

 

    [sELECT from table where id=$i]

    [if no record, INSERT one with id=$i, name=NULL]

    $i++;

}

[xtopolis]

What database is on the system you want to pull from?  Can you export the data to a common format such as CSV, or something?  You could even write code to create the mysql statement to copy paste if you wanted.  (all this should/would keep the data intact).

 

What thorpe was hinting at is why would you have missing data in an auto increment field?  IDs that are no longer used should at least have an "enabled?" column, and simply be disabled when no longer needed afaik.

[mameha]

Yes I guess the admin of previous system just deleted some rows instead of setting 'deleted' to 1.

 

The data is right now on MySQL and yes I can export to csv.

[mameha]

fixed with:

 

$i=0;
while ($i<6000){

		$query = "SELECT title FROM `table` WHERE id = '".$i."'";
		$result = mysql_query($query);
		if(mysql_num_rows($result) != 1) {
			// insert new
			$query = "INSERT INTO `table` ( `id` , `entry_date`, `title`, `status_name`, `vip_mark` ) VALUES ('".$i."', '2008-10-28 01:00:00', 'NULL', 'Completed', '0');";			
			echo $i."<br/>";
		}
		$result = mysql_query($query);

    			$i++;
} 

echo 'done';

[xtopolis]

edit, I don't see why you're filling null spots .. it seems unnecessary since you can set the auto index...

------------

I'm going to say use "mysqldump" (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html).

However I can't be more helpful since I use phpmyadmin and I don't see the commands it uses behind the scenes.

 

This should give you something that you could copy/paste into your new database, keeping the data in order.  Obviously you wouldn't delete the old data until you verified it was successful.

 

Otherwise save it to CSV and import it into mysql.

 

 

If you choose the first option, is should keep the auto index in the correct spot so as not to fill in previous deleted ids.  You can always alter the table to reflect the auto index you want as well.