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.