MySQL Forums
Forum List  »  MySQL Query Browser

Re: merging tables
Posted by: John M
Date: April 22, 2010 08:18AM

Yea i know that is a bit awkward. I managed to do this.
I create a table with all the entries that are the same on both table by combining three fields "NAME","SURNAME" and "PHONE1" on both tables.Here is the query:

create table exist as (
SELECT distinct
cl2.`ID`, cl2.`COMPANY`, cl2.`SURNAME`, cl2.`NAME`, cl2.`PHONE1`, cl2.`PHONE2`, cl2.`FAX`, cl2.`CELLULAR1`, cl2.`CELLULAR2`, cl2.`AFM`, cl2.`DOY_ID`, cl2.`DOY_TEXT`, cl2.`PROFESSION_ID`, cl2.`PROFESSION_TEXT`, cl2.`COMPTYPE_ID`, cl2.`COMPTYPE_TEXT`, cl2.`EMAIL`, cl2.`WEB`, cl2.`IDCARD`, cl2.`ADDRESS`, cl2.`NUMBER`, cl2.`TAXCODE`, cl2.`DISTRICT_ID`, cl2.`DISTRICT_TEXT`, cl2.`CITY_ID`, cl2.`CITY_TEXT`, cl2.`COUNTRY_ID`, cl2.`COUNTRY_TEXT`, cl2.`AM`, cl2.`AMKA`, cl2.`PATRONIMO`, cl2.`MHTRONIMO`, cl2.`COMM_STATUS_ID`
FROM clients1 cl1,clients2 cl2 where cl1.SURNAME = cl2.SURNAME AND cl1.NAME = cl2.NAME
and cl1.PHONE2 = cl2.PHONE2)

Because PHONE1 column have duplicates and null entries it was hard to avoid them cause both tables have thousand of entries.

After that I've used this query

insert into clients1
(COMPANY,SURNAME,NAME,PHONE1,PHONE2,FAX,CELLULAR1,CELLULAR2,
AFM,DOY_ID,DOY_TEXT,PROFESSION_ID,PROFESSION_TEXT,COMPTYPE_ID,COMPTYPE_TEXT,EMAIL,WEB,
IDCARD,ADDRESS,NUMBER,TAXCODE,DISTRICT_ID,DISTRICT_TEXT,CITY_ID,CITY_TEXT,
COUNTRY_ID,COUNTRY_TEXT,AM,AMKA,PATRONIMO,MHTRONIMO,COMM_STATUS_ID)
SELECT distinct
cl2.`COMPANY`, cl2.`SURNAME`, cl2.`NAME`, cl2.`PHONE1`, cl2.`PHONE2`, cl2.`FAX`, cl2.`CELLULAR1`, cl2.`CELLULAR2`, cl2.`AFM`, cl2.`DOY_ID`, cl2.`DOY_TEXT`, cl2.`PROFESSION_ID`, cl2.`PROFESSION_TEXT`, cl2.`COMPTYPE_ID`, cl2.`COMPTYPE_TEXT`, cl2.`EMAIL`, cl2.`WEB`, cl2.`IDCARD`, cl2.`ADDRESS`, cl2.`NUMBER`, cl2.`TAXCODE`, cl2.`DISTRICT_ID`, cl2.`DISTRICT_TEXT`, cl2.`CITY_ID`, cl2.`CITY_TEXT`, cl2.`COUNTRY_ID`, cl2.`COUNTRY_TEXT`, cl2.`AM`, cl2.`AMKA`, cl2.`PATRONIMO`, cl2.`MHTRONIMO`, cl2.`COMM_STATUS_ID`
from clients2 cl2 where cl2.ID not in (select ex.ID from exist ex);

and put all the non exist records to the clients1 table and all seem to be ok.
Again, I'm not 100% sure that i didn't create any duplicates but my boss is happy and that's ok.

Options: ReplyQuote


Subject
Written By
Posted
April 21, 2010 07:48AM
April 21, 2010 08:51AM
April 22, 2010 02:10AM
April 22, 2010 08:06AM
Re: merging tables
April 22, 2010 08:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.