Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> How best to to this in SQL - Update table

How best to to this in SQL - Update table

From: Sanjay Raj <rsanjaynj_at_yahoo.com>
Date: 17 Jan 2002 07:25:23 -0800
Message-ID: <9f767483.0201170725.7cb05e1e@posting.google.com> 





I have two tables ( A & B) and a flat file which I plan to load into a
third table &#8211; Table C



SQL> desc TableA; ( rows :1.8 million)


Name	Null?	Type
                

ACCTID	NOT NULL	VARCHAR2(16)
ACCTSUBTYPE		VARCHAR2(30)
ADDR1		VARCHAR2(30)
ADDR2		VARCHAR2(30)
APPROXAGE		NUMBER(5)
CITY		VARCHAR2(30)
DEGREE		VARCHAR2(15)
FIRSTNM		VARCHAR2(20)
LASTNM		VARCHAR2(20)
MENUMBER		VARCHAR2(12)
MIDDLENM		VARCHAR2(20)
NAME		VARCHAR2(50)
PHONENUM		VARCHAR2(15)
REPSPECCODE		VARCHAR2(15)
STATELICENSENUM		VARCHAR2(20)
STATELICENSENUMEXP		DATE
STPROVCODE		VARCHAR2(15)
TERRITORYID	NOT NULL	VARCHAR2(12)
TGTIND		VARCHAR2(15)
TITLE		VARCHAR2(15)
USRGRPIND		VARCHAR2(15)
ZIPPC		VARCHAR2(10)
REFERENCEDEANUM		VARCHAR2(9)
ALTERNATEID		VARCHAR2(12)
FAXNUM		VARCHAR2(13)
columnB		VARCHAR2(3)
EMAILADD		VARCHAR2(30)
columnC		VARCHAR2(15)
READELETE		VARCHAR2(15)
ROLE		VARCHAR2(15)
ColumnX		VARCHAR2(30)
columnY		VARCHAR2(30)
columnZ		VARCHAR2(30)
ColumnA		VARCHAR2(30)


SQL> Desc TableB; (700,000 rows)
 Name	Null?	Type
 		
 ACCTID	NOT NULL	VARCHAR2(16)
 ACCTSUBTYPE		VARCHAR2(30)
 ACCTTYPE		VARCHAR2(15)
 AFFACCTID	NOT NULL	VARCHAR2(16)
 AFFACCTSUBTYPE		VARCHAR2(30)
 AFFACCTTYPE		VARCHAR2(15)
 CALLLOCTYPECODE		CHAR(1)
 CMMNT		VARCHAR2(30)
 TERRITORYID	NOT NULL	VARCHAR2(12)
SQL> desc tableC; (loaded from the flat file) 1000 rows
Name	Null?	Type
		
ACCTID	Not Null	VARCHAR2(16)
TERRITORYID	Not Null	VARCHAR2(8)
FIRSTNM		VARCHAR2(20)
MIDDLENM		VARCHAR2(20)
LASTNM		VARCHAR2(20)
DEGREE		VARCHAR2(15)
PREFIX		VARCHAR2(10)
ADDR1		VARCHAR2(30)
ADDR2		VARCHAR2(30)
CITY		VARCHAR2(30)
STPROVCODE		VARCHAR2(15)
ZIPPC		VARCHAR2(10)
PHONENUM		VARCHAR2(15)
ALTERNATEID		VARCHAR2(12)
MENUMBER		VARCHAR2(12)
COlumnA		VARCHAR2(12)
REFERENCEDEANUM		VARCHAR2(9)
ColumnB		VARCHAR2(9)
STATELICENSENUM		VARCHAR2(20)
AFFACCTID	Not Null	VARCHAR2(16)
NEW_ACCTID		VARCHAR2(16)
columnC		VARCHAR2(16)






This is what I want to do: 



Over a period of time the data in Table A and table B have become
corrupt ( because of the bad design). The flat file ( table C)
contains correct data. Plan is to update the following columns in
table A and Table B with data from table C. The change in data may be
in the form of ADDR, LASTNM fields being  changed or in a few (approx
300) cases the ACCTID being updated with the NEW_ACCTID from table C.
A condition might exist such that the ACCTID,TERRITORYID combination
in table C does not exist in Table A or Table C. What has to be done
in such a case is to insert the whole record from Table C into table A
or Table B based on the ACCTID. For Example:



Table A:


ACCTID           TERRITORYID



---------------- ------------



7329414593ZEN    06432053



7329414593ZEN    01402B3B


Table C:



ACCTID           TERRITORYID



---------------- --------



7329414593ZEN    0110805A



So in this case we have to insert into Table A and Table B the full
record ( only the required columns) from Table C.



So the following record will be inserted in Table A
7329414593ZEN,0110805A,ROBERT,ALLEN,5601	W. EUGIE,,GLENDALE,AZ,

85304,1001710041,BA1028174,BA1028174,AZ13801,-1777597859 &#8230;.
Columns which are in table A but not in TableC.
Fields in Table A to be updated from data in Table C



ACCTID,TERRITORYID,FIRSTNM,MIDDLENM,LASTNM,DEGREE, ADDR1,

ADDR2,CITY,STPROVCODE,ZIPPC,PHONENUM,ALTERNATEID,MENUMBER

ColumnA,REFERENCEDEANUM,ColumnB,STATELICENSENUM,
NEW_ACCTID ( this in  a few hundred cases when the ACCTID is changed
i.e NEW_ACCTID in table C is not null),


ColumnC



Fields in Table B to be updated from data in Table C



ACCTID,TERRITORYID, AFFACCTID


What is the best way to achieve this?



Sample Data from Table C



SQL> select acctid||'|'||territoryid||'|'||firstnm||'|'||
2  lastnm||'|'||alternateid||'|'||REFERENCEDEANUM||'|'||New_acctid
3  from tableC


4  where rownum <51 and new_acctid is not null
SQL> /


ACCTID||'|'||TERRITORYID||'|'||FIRSTNM||'|'||LASTNM||'|'||ALTERNATEID||'|'||REFE





-2025844254|0140804A|JAMES|LINDSEY|||-2025844254


2219573374|0140806A|JOHN|"REAVES||102871090|2219573375


8564157930IL|0451501H|ROBERT|WARREN|767358|BW4474639|8564157930ILCR




-579076020|0180233C|ELIZABETH|RAMOS|||-579076020CR



251060183OIL|0150350M|KATHY|ROSEN|||251060183OILCR
8464257011IL|0150452M|AYMAN|AL-REJLEH|1749759|BA4508101|8464257011ILCR
7152054910IL|0120850M|SHEZI|KUNDI|1993738||7152054910ILCR
215100189OIL|0130903A|SHAKEEL|AHMAD|2174434082||-2127032886
152060058OIL|0446107O|JOHN|LYNCH|2028776998||-1199046247
152060058OIL|0446107O|JOHN|LYNCH|2028778112||-1420820441
9120455970IL|0180303A|WILLIAM|BROWN|2028890080||-2007497291
856289788OIL|0170104A|ELISABETH|BERGMAN|472928|AB2005723|856289788OILMS
856289788OIL|0170104A|ELISABETH|BERGMAN|472928|AB2005723|856289788OILMS
856289788OIL|0170104A|ELISABETH|BERGMAN|472928|AB2005723|856289788OILMS
2530554568IL|0160854M|VINH|PHAM|1982140||2530554568ILMS
044040169OIL|0160251M|THOMAS|RUSSELL|1873721|BR5213599|044040169OILMS
044040169OIL|0160251M|THOMAS|RUSSELL|1873721|BR5213599|044040169OILMS
635209727OIL|0101051M|DENNIS|AMUNDSON|1273209|AA3046895|635209727OILMS
6461257591IL|0110706A|DAVID|GONZALES|||6461257591ILMS
6461257591IL|0110732C|DAVID|GONZALES|378616|BG0504414|6461257591IL




-968621832|0190901A|MAXINE|HAMILTON|1743835|BH1711464|-968621832MS



2865759147|0190602A|PAUL|WALLACE|1571797|BW4768555|2865759147




-1541665373|0150803A|MICHAEL|DAY|1955063|BD6524715|-1541665373


7329403361ZEN|0190202A|R|REYNOLDS|172178|BR0211564|7329403361ZENMS


074076943OIL|0130750M|RICHARD|NOVAK|749457|AN2367630|074076943OIL
074076943OIL|0130750M|RICHARD|NOVAK|749457|AN2367630|074076943OIL
074106959OIL|0130751M|RICHARD|NOVAK|749457|AN2367630|074106959OIL
646019338OIL|0110603A|J KENT|NELSON|15377|AN5518711|646019338OILMS
264503436|0471503I|STEVEN|GUTNIK|61806|AG2710540|264503436MS
264503436|0426503O|STEVEN|GUTNIK|61806|AG2710540|264503436MS


229376258|0446607O|EUGENE|OLIVERI|229947|AO3104318|229376258MS


013080454OIL|0100733C|JAY|WINNER|763892|BW1535838|013080454OILMS
253050309OIL|0160855M|HENRY|BODENHEIMER|1707860|BB2775041|253050309OILMS
6562155562IL|0110550M|DAVE|FALING|||6562155562ILMS
842085505BIL|0150452M|DAVID|WOOD|415613|BW2857209|842085505BILMS
2360351973IL|0110551M|JEFF|ALBRECHT|415708|BA0869492|2360351973ILMS




-1909193694|0180504A|MICHAEL|RICE|1980344||-1909193694MS



214090029OIL|0110502A|MARKLE|KARLEN|38378|AK3632153|214090029OILMS
6562356381IL|0110503A|GARY|MILLER|39231|AM7856480|6562356381ILMS
6562155250IL|0110550M|COLLEEN|COOPER|178542|AC1200132|6562155250ILMS
214090113OIL|0110503A|LLOYD|BANASZAK|419212|AB2541250|214090113OILMS




-1876688799|0110531C|STEPHANIE|KIVI|1339464|BK4511677|-1876688799MS



027030534OIL|0140703A|DAVID|SULLIVAN|6019821283||244842680
8562957924IL|0170105A|EMANUEL|KRIFCHER|475679|AK5033458|8562957924ILMS
8464259250IL|0150452M|ASHFAQ|AHMAD|2010715||8464259250ILcr
0333253500IL|0150251M|ROKSANA|ALI|1992615||0333253500ILcr
236030403OIL|0110551M|DAVID JOHN|RHUDE|827283|BR0543199|236030403OILMS
223090042OIL|0180352M|MICHAEL|GOLD|1715732|AG6386494|223090042OILMS
945269443OIL|0160951M|LAWRENCE|FRIEDMAN|||945269443OILMS
222110060OIL|0170650M|FRANK|MCGEEHIN|453480|AM1483837|222110060OILMS





50 rows selected.



Running Oracle Version : Oracle7 Server Release 7.3.4.5.0 on HP UX
11.00




Thanks


please email yr answers
Received on Thu Jan 17 2002 - 09:25:23 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US