Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Location
    USA
    Posts
    4

    Question Unanswered: Replace table structure

    Hi, everyone,

    I am wondering if anyone could let me know how I can replace an entire table structure for another one? I am trying to upload a backup file into a new database but the structures are different, so it won't work.

    Here is an example:

    I want to replace this table:
    `members` (
    `id` mediumint(8) NOT NULL default '0',
    `name` varchar(255) NOT NULL default '',
    `mgroup` smallint(3) NOT NULL default '0',
    `legacy_password` varchar(32) NOT NULL default '',
    `email` varchar(255) NOT NULL default '',
    `joined` int(10) NOT NULL default '0',
    `ip_address` varchar(16) NOT NULL default '',
    `posts` mediumint(7) default '0',
    `title` varchar(64) default NULL,
    `allow_admin_mails` tinyint(1) default NULL,
    `time_offset` varchar(10) default NULL,
    `hide_email` varchar(8) default NULL,
    `email_pm` tinyint(1) default NULL,
    `email_full` tinyint(1) default NULL,
    `skin` smallint(5) default NULL,
    `warn_level` int(10) default NULL,
    `warn_lastwarn` int(10) NOT NULL default '0',
    `language` varchar(32) default NULL,
    `last_post` int(10) default NULL,
    `restrict_post` varchar(100) NOT NULL default '0',
    `view_sigs` tinyint(1) default '1',
    `view_img` tinyint(1) default '1',
    `view_avs` tinyint(1) default '1',
    `view_pop` tinyint(1) default '1',
    `bday_day` int(2) default NULL,
    `bday_month` int(2) default NULL,
    `bday_year` int(4) default NULL,
    `new_msg` tinyint(2) default NULL,
    `msg_total` smallint(5) default '0',
    `show_popup` tinyint(1) default NULL,
    `misc` varchar(128) default NULL,
    `last_visit` int(10) default '0',
    `last_activity` int(10) default '0',
    `dst_in_use` tinyint(1) default '0',
    `view_prefs` varchar(64) default '-1&-1',
    `coppa_user` tinyint(1) default '0',
    `mod_posts` varchar(100) NOT NULL default '0',
    `auto_track` varchar(50) default '0',
    `org_perm_id` varchar(255) default '',
    `temp_ban` varchar(100) default '0',
    `login_anonymous` char(3) NOT NULL default '0&0',
    `ignored_users` text NOT NULL,
    `mgroup_others` varchar(255) NOT NULL default '',
    `member_login_key` varchar(32) NOT NULL default '',
    `subs_pkg_chosen` smallint(3) NOT NULL default '0',
    `sub_end` int(10) NOT NULL default '0',
    `map` varchar(100) default NULL,
    `map_location` varchar(15) default NULL,
    `map_coords` varchar(30) default NULL,
    PRIMARY KEY (`id`),
    KEY `name` (`name`),
    KEY `mgroup` (`mgroup`),
    KEY `bday_day` (`bday_day`),
    KEY `bday_month` (`bday_month`)
    ) TYPE=MyISAM;



    For this one:

    `members` (
    `id` mediumint(8) NOT NULL default '0',
    `name` varchar(255) NOT NULL default '',
    `mgroup` smallint(3) NOT NULL default '0',
    `email` varchar(150) NOT NULL default '',
    `joined` int(10) NOT NULL default '0',
    `ip_address` varchar(16) NOT NULL default '',
    `posts` mediumint(7) default '0',
    `title` varchar(64) default NULL,
    `allow_admin_mails` tinyint(1) default NULL,
    `time_offset` varchar(10) default NULL,
    `hide_email` varchar(8) default NULL,
    `email_pm` tinyint(1) default '1',
    `email_full` tinyint(1) default NULL,
    `skin` smallint(5) default NULL,
    `warn_level` int(10) default NULL,
    `warn_lastwarn` int(10) NOT NULL default '0',
    `language` varchar(32) default NULL,
    `last_post` int(10) default NULL,
    `restrict_post` varchar(100) NOT NULL default '0',
    `view_sigs` tinyint(1) default '1',
    `view_img` tinyint(1) default '1',
    `view_avs` tinyint(1) default '1',
    `view_pop` tinyint(1) default '1',
    `bday_day` int(2) default NULL,
    `bday_month` int(2) default NULL,
    `bday_year` int(4) default NULL,
    `new_msg` tinyint(2) default '0',
    `msg_total` smallint(5) default '0',
    `show_popup` tinyint(1) default '0',
    `misc` varchar(128) default NULL,
    `last_visit` int(10) default '0',
    `last_activity` int(10) default '0',
    `dst_in_use` tinyint(1) default '0',
    `view_prefs` varchar(64) default '-1&-1',
    `coppa_user` tinyint(1) default '0',
    `mod_posts` varchar(100) NOT NULL default '0',
    `auto_track` varchar(50) default '0',
    `temp_ban` varchar(100) default '0',
    `sub_end` int(10) NOT NULL default '0',
    `login_anonymous` varchar(3) NOT NULL default '0&0',
    `ignored_users` text,
    `mgroup_others` varchar(255) NOT NULL default '',
    `org_perm_id` varchar(255) NOT NULL default '',
    `member_login_key` varchar(32) NOT NULL default '',
    `member_login_key_expire` int(10) NOT NULL default '0',
    `subs_pkg_chosen` smallint(3) NOT NULL default '0',
    `has_blog` tinyint(1) NOT NULL default '0',
    `has_gallery` tinyint(1) NOT NULL default '0',
    `members_markers` text,
    `members_editor_choice` varchar(3) NOT NULL default 'std',
    `members_auto_dst` tinyint(1) NOT NULL default '1',
    `members_display_name` varchar(255) NOT NULL default '',
    `members_created_remote` tinyint(1) NOT NULL default '0',
    `members_cache` mediumtext,
    `members_disable_pm` int(1) NOT NULL default '0',
    `members_l_display_name` varchar(255) NOT NULL default '0',
    `members_l_username` varchar(255) NOT NULL default '0',
    `failed_logins` text,
    `failed_login_count` smallint(3) NOT NULL default '0',
    `members_profile_views` int(10) unsigned NOT NULL default '0',
    PRIMARY KEY (`id`),
    KEY `members_l_display_name` (`members_l_display_name`),
    KEY `members_l_username` (`members_l_username`),
    KEY `mgroup` (`mgroup`),
    KEY `bday_day` (`bday_day`),
    KEY `bday_month` (`bday_month`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this new table, will it load its own data rows?

    in other words, do you need the data in the old table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    USA
    Posts
    4
    Quote Originally Posted by r937
    this new table, will it load its own data rows?

    in other words, do you need the data in the old table?
    Yes, I definitely need to the old data... It is a Forum with nearly 100.000 posts and over 2.000 members.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you know the column-to-column mapping that is required?

    you will do this in steps --

    CREATE TABLE newtable ( ... )

    INSERT INTO newtable ( columns )
    SELECT columns or expressions
    FROM oldtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    USA
    Posts
    4
    Hmmmm, I'm not sure I understand... I already have the new table. I just need to insert the data from the old one, but it is not compatible because the new one has different values. It also looks like the order of those values matter. If you want, I can send you the files for both tables.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mille
    If you want, I can send you the files for both tables.
    no, please don't do that

    have you ever seen INSERT SELECT syntax? do you know what's necessary?

    list the columns of the receiving table, and then write the SELECT to match column for column

    for example

    INSERT INTO newtable ( name, hatsize, IQ )
    SELECT username, 7.5, 145 from users

    so you would list every column in your new table and match that with whatever columns from your old table, with constants and/or defaults and/or NULLs as necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Location
    USA
    Posts
    4
    I know the INSERT command, but not the INSERT SELECT one. I will give it a try and see if I understand it. I'll let you know if I managed. Thanks.

Posting Permissions

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