Merging tables - Dev Shed

Dev Shed Forums Sponsor:

VeriSign Code Signing Digital Certificates provides assurance to end users. Read about this and more in the free white paper: “How to Digitally Sign Downloadable Code for Secure Content Transfer.” Learn More!

January 20th, 2004, 09:27 PM

Hypex

Junior Member

Join Date: Jan 2004

Posts: 8

Time spent in forums: < 1 sec
Reputation Power: 0

Merging tables

Hi there, sorry new to mysql and have a question.

I have to tables both with the same column names and structure that i want to merge into one.

Table A has some infomation that is the same in Table B.

How do i merge Table B into Table A without having duplcate data?

Ive tried this.

insert into tableA select * from tableB;

but i get dupe information
(example in the part no column ill have two 20002 after the merge cause it was in both tables originally)

January 20th, 2004, 10:58 PM

victorpendleton

Contributing User

Join Date: Jan 2003

Location: No es importante

Posts: 2,065

Time spent in forums: 6 h 31 m 56 sec
Reputation Power: 8

Do these tables have primary keys? What version of MySQL are you using?

__________________
El éxito consiste en una serie de pequeñas victorias día a día

MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html

January 20th, 2004, 11:02 PM

Hypex

Junior Member

Join Date: Jan 2004

Posts: 8

Time spent in forums: < 1 sec
Reputation Power: 0

no neither tables have unique field, and the lastest version of mysql (im pretty sure ne way)

January 20th, 2004, 11:33 PM

abombss

Contributing User

Join Date: Jan 2004

Posts: 49

Time spent in forums: < 1 sec
Reputation Power: 5

You can try this... It is untested but I think it will get you close.

Adam

Code:

insert into tableA 
select b.*
from tableB b natural left outer join tableA a
where a.col1 is null

January 20th, 2004, 11:59 PM

Hypex

Junior Member

Join Date: Jan 2004

Posts: 8

Time spent in forums: < 1 sec
Reputation Power: 0

Quote:

Originally posted by abombss
You can try this... It is untested but I think it will get you close.

Adam

Code:

insert into tableA select b.* from tableB b natural left outer join tableA a where a.col1 is null

okay so
TableA= rocket
TableB- russell2
.col1 = partno (name of column on) right?

i should get

insert into rocket
select *
from russell2 b natural left outer join rocket a
where a.partno is null
?

i know i have inturpreted this wrong cause im new, apoligies in advance.

January 21st, 2004, 12:01 AM

victorpendleton

Contributing User

Join Date: Jan 2003

Location: No es importante

Posts: 2,065

Time spent in forums: 6 h 31 m 56 sec
Reputation Power: 8

He is going to need primary keys to ensure that duplicate values are not inserted.
...
What column is shared between these two tables?

January 21st, 2004, 12:02 AM

Hypex

Junior Member

Join Date: Jan 2004

Posts: 8

Time spent in forums: < 1 sec
Reputation Power: 0

both tables have

Partno | descrtion | comment

January 21st, 2004, 12:16 AM

victorpendleton

Contributing User

Join Date: Jan 2003

Location: No es importante

Posts: 2,065

Time spent in forums: 6 h 31 m 56 sec
Reputation Power: 8

If the combination of these three columns is unique you could create an unique index on both tables and create a temporary table that has the difference of table1 and table2
insert
into table1
select *
from table2 t2, table1 t1
where t2.col1 <> t1.col1 and t2.col2 <> t1.col1 and t2.col3 <> t1.col3
...
Try this out and let us know.

January 21st, 2004, 12:45 AM

Hypex

Junior Member

Join Date: Jan 2004

Posts: 8

Time spent in forums: < 1 sec
Reputation Power: 0

Code:

insert
into rocket
select *
from russell2 t2, rocket t1
where t2.col1 <> t1.col1 and t2.col2 <> t1.col1 and t2.col3 <> t1.col3

error:

SQL-query :

INSERT INTO rocket SELECT * FROM russell2 t2, rocket t1 where t2.col1 <> t1.col1 and t2.col2 <> t1.col1 and t2.col3 <> t1.col3

MySQL said:

INSERT TABLE 'rocket' isn't allowed in FROM table list

#10

January 21st, 2004, 12:57 AM

abombss

Contributing User

Join Date: Jan 2004

Posts: 49

Time spent in forums: < 1 sec
Reputation Power: 5

The newer version of mysql will let you do this.

The natural left outer join does a left outer join on the columns in the two tables with the same name. It is just a shortcut. The <> is not the proper way to do the join.

Here are a couple more ways.

Code:

create table temp1 select * from rocket;

truncate table temp1;

create unique index on temp1 (col1, col2, col3);

insert ignore temp1
select *
from rocket;

insert ignore temp1
select *
from russell2;

Code:

create table temp1 select * from rocket;

truncate table rocket;

insert into rocket
select * from russell2
union select * from temp1;

Run each query seperatly do not run a batch, the truncate command will kill all the data in the rocket table so be careful!

If that doesn't work... your version of mysql does not support unions yet which sucks! Then try this.

Code:

create table temp1 select * from rocket;

truncate table rocket;

insert into rocket
select a.* from temp1 a natural inner join russell2 b;

insert into rocket
select a.* from temp1 a natural leftr outer join russell2 b
where b.col1 is null and b.col2 is null and b.col3 is null;

insert into rocket
select b.* from temp1 a natural leftr outer join russell2 b
where a.col1 is null and a.col2 is null and a.col3 is null;

Good Luck... If this works please add a primary key to the table which would make this whole thing ten times easier.

Adam

Last edited by abombss : January 21st, 2004 at 01:04 AM.

#11

January 21st, 2004, 09:20 AM

victorpendleton

Contributing User

Join Date: Jan 2003

Location: No es importante

Posts: 2,065

Time spent in forums: 6 h 31 m 56 sec
Reputation Power: 8

You failed to read my post properly and I failed to explain clearly.

Quote:

create a temporary table that has the difference of table1 and table2

...

#12

January 21st, 2004, 04:10 PM

Hypex

Junior Member

Join Date: Jan 2004

Posts: 8

Time spent in forums: < 1 sec
Reputation Power: 0

Quote:

Originally posted by victorpendleton
You failed to read my post properly and I failed to explain clearly.

...

No you explained clearly it was my stupidty that made the mistake

I would like to thank both abombss and victorpendleton for your help. Both ways worked and the support was great!

thanks once again ( I have a paid help service that wasnt as good as this ).

Last edited by Hypex : January 21st, 2004 at 04:12 PM.

Viewing: Dev Shed Forums > Databases > MS SQL Development > Merging tables

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!