MySQL and Merge Table


Hi, this is just an information, i need to keep it over here for my future reference. How to a table with the data collected from two different tables?

Mysql offers the MERGE option. a MERGE table is a collection of identical MyISAM* tables those can be used as one! Identical means, all tables shud have identical columns and index Information.

here is what the query I have used to test it.

create database pandian;

use pandian;
/*table 1,mumbai*/
create table mumbai
(first_name varchar(30),
amount int(10));

/*table 2,mumbai*/
create table delhi
(first_name varchar(30),
amount int(10)
);

/*Insert data*/
insert into mumbai values (‘ranjini’, 75);
insert into mumbai values (‘angel’, 55);
insert into mumbai values (‘mahendran’, 55);
insert into mumbai values (‘veera’, 55);
insert into mumbai values (‘pandian’, 55);
insert into mumbai values (‘balu’, 55);

insert into delhi values (‘kavitha’, 25);
insert into delhi values (‘raman’, 68);
insert into delhi values (‘anbu’, 45);
insert into delhi values (‘selva’, 98);
insert into delhi values (‘nalini’, 25);
insert into delhi values (‘amutha’, 36);

/*new table with the data from mumbai and delhi*/
create table total (
name varchar(30),
cash int (10)
) type=merge union=(mumbai, delhi);

/*check whether any changes made to individual tables are getting reflected in the total table, wow yes!*/
insert into delhi values (‘richard’, 75);

/*what will happen if the resultant table is of different structure??*/
create table total_different (
name varchar(15),
cash int (10)
) type=merge union=(mumbai, delhi);
/*
oops.. Gone!

Unable to open underlying table which is differently defined or of non-MyISAM type or doesn’t exist
*/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s