+ Post New Thread
Results 1 to 3 of 3
General Chat Thread, MYSQL Help in General; I have a table that i need to duplicate the contents of and write it back to the table. e.g ...
  1. #1

    Join Date
    Apr 2006
    Location
    UK
    Posts
    939
    Thank Post
    39
    Thanked 70 Times in 54 Posts
    Rep Power
    30

    MYSQL Help

    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?

  2. #2
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    5,007
    Thank Post
    124
    Thanked 286 Times in 263 Posts
    Rep Power
    109

    Re: MYSQL Help

    Set ID to be self incrementing?

  3. #3

    maniac's Avatar
    Join Date
    Feb 2007
    Location
    Kent
    Posts
    3,087
    Thank Post
    210
    Thanked 432 Times in 312 Posts
    Rep Power
    145

    Re: MYSQL Help

    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.



SHARE:
+ Post New Thread

Similar Threads

  1. My Little Helpdesk and MySQL
    By WithoutMotive in forum Windows
    Replies: 7
    Last Post: 25th April 2008, 03:56 PM
  2. Joomla - mysql
    By smith1195 in forum Web Development
    Replies: 3
    Last Post: 13th November 2007, 05:16 PM
  3. MySQL Databases
    By timbo343 in forum Windows
    Replies: 12
    Last Post: 1st May 2007, 12:46 PM
  4. Access DB to MySQL
    By Gatt in forum Web Development
    Replies: 3
    Last Post: 29th November 2006, 11:32 PM
  5. php MySql
    By iking in forum Books and Manuals
    Replies: 0
    Last Post: 11th July 2005, 04:35 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •