CODEWITHSUNDEEP
mysqlsql
liveth19937
liveth19937

Reputation: 61

Merging two tables in one

I just need to merge two tables (to player_new) without conflict.

In table 1 (player_new) I have 65,000 records. In table 2 (player_old) I have 47,500 records.

Table structure for both are:

-- ----------------------------
-- Table structure for player_new
-- ----------------------------
DROP TABLE IF EXISTS `player_new`;
CREATE TABLE `player_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL DEFAULT '0',
  `name` varbinary(24) NOT NULL DEFAULT 'NONAME'
  ........................
) ENGINE=MyISAM AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for player_old
-- ----------------------------
DROP TABLE IF EXISTS `player_old`;
CREATE TABLE `player_old` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL DEFAULT '0',
  `name` varbinary(24) NOT NULL DEFAULT 'NONAME'
  ........................
) ENGINE=MyISAM AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1;

There some names are duplicated and I just need give same names to %s_x (in player_new table), so player can change his name later.

Any ideas?

Upvotes: 1

Views: 82

Answers (3)

Rahul
Rahul

Reputation: 77866

You should probably try restructuring your table as suggested by @echo_me, but still what you want can be achieved by merging both the table data to a separate table and then renaming that new table to player_new as below. See a demo fiddle here

create table merged_player (
  `id` int(11) NOT NULL AUTO_INCREMENT primary key,
  `account_id` int(11) NOT NULL DEFAULT '0',
  `name` varbinary(24) NOT NULL DEFAULT 'NONAME'
);

insert into merged_player(account_id,name)
select account_id,name from player_new
union
select account_id,name from player_old;

drop table player_new;

rename table merged_player to player_new;

Upvotes: 1

shekhar
shekhar

Reputation: 937

As you mentioned both tables are having same table description . we can use some logic like 

insert into table1
select * from table2

I mean for your context its simply 

insert into player_new
select * from player_old

I have done small try , just look at the image.

enter image description here

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

why dont you use just one table like that

CREATE TABLE `player` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL DEFAULT '0',
`Newname` varbinary(24)  DEFAULT 'NONAME',
`Oldname` varbinary(24)  DEFAULT 'NONAME'
 ......
 ) ENGINE=MyISAM AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1;

then when you insert new name just update Newname column. and let Oldname

Upvotes: 0

Related Questions