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
*/
