MYSQL Help

[danIT]

I have a table that i need to duplicate the contents of and write it back to the table. e.g

ID subject memo
1 12 text...
2 24 text...
3 38 text...
4 45 text...
5 78 text...
6 45 text...
7 69 text...
8 46 text...

Example of the table above, I need to copy the contents of the table then write back the values but change the ID colum. The ID must be unique and there are over 3000 entries, anyone got any ideaS?

[ChrisH]

Set ID to be self incrementing?

[maniac]

Not fully understanding exactly what you're after, but I'll have a guess.

Use the mySQL console logged in as an admin level user.

First create a blank copy of the database sructure with the correct field names and attributes set, (as far as I know there's no command to actually do this) so you'll have to create it manually. Then use the command;

INSERT INTO `databasename`.`newtablename` SELECT * FROM `databasename`.`oldtablename` ;

That will duplicate the contents into the empty table.

Then if you want to add a unique identifier, use a command like;

ALTER TABLE `newtablename` ADD `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

which will insert a new field at the start called 'ID' and auto increment the value for every record, thus giving you a unique ID per record. It auto populates this for the existing records when you create it.

If you want to remove the old ID number field use the command;

ALTER TABLE `newtablename` DROP `OLDID`

which will remove the old ID field, thus giving you a copy of the database with an auto incrementing unique ID field for each record.

As an alturnative to create the copy, you could also back the database up to a file, then restore it to a different table name, but the commands to do that escape me at the moment.

If you're running PHP in conjunction with mySQL, you could also write some simple code to read each value individually, and write it back to a new table.

Mike.