Newbie -Can this done using SQL

From: Sanjay Raj <rsanjaynj_at_yahoo.com>
Date: 9 Oct 2001 12:04:17 -0700
Message-ID: <9f767483.0110091104.735452b0@posting.google.com>

Hey Oracle Gurus I need a help.
Might be simple. Here is the description of the problem

I have a table USERLIST. The table has 110 columns (not my design- I inherited it) . The layout is

ACCOUNTID		VARCHAR2(16)
ZONEID		VARCHAR2(12)
FIRSTNM		VARCHAR2(20)
LASTNM		VARCHAR2(20)
CITY			VARCHAR2(30)
STATE			VARCHAR2(15)
ZIP			VARCHAR2(10)
next column;

next column;
next column;

The data looks like

-12345IL, 0415404V, AUSTEN,CHEN,NEW YORK,NY,10011
125438,0419904A, NATHAN,BROWN,SAN FRANCISCO,CA,94143

247090167OIL,0151050M,CURT,WEBER,Akron,OH,44304
7222057300IL,0141051M,RON,BAUM,,,
7222058190IL,0141051M,PETER,JOHNSON,NEW ORLEANS,LA,70146

2068842210,0415209V,PAUL,BEFFA,SPRINGFIELD,MO,65807 7222060580IL,0141051M,Bill,MERRILL,NEW ORLEANS,LA,70146

379322577, 09840Y0M, GLADSTONE,JONES,CLEVELAND,OH,441061736
380370953,0960350M,MARY,GALLAGHER,CINCINNATI,OH,452192364
380370953,0150850M,MARY,GALLAGHER,Cincinnati,OH,45237


-1087634317,01703Y0C,MICHAEL,RUPP,Wilkes-Barre,PA,18701

-1833696950,01806Z5A,Raymond,Lackore,Va. Beach,VA,23454

-1087634432,01703Y0C,Art,Heal,Scranton,PA,18510
-1087634432,01703Z0C,Art,Heal,Scranton,PA,18510

Looking at the ZONEID column (position 2) in the last four (4) records above

For the 4th Last record above

-1087634317,01703Y0C,MICHAEL,RUPP,Wilkes-Barre,PA,18701

Here there is Y in the 6th position of the ZONEID (01703Y0C) column and for the same ACCTID (-1087634317) there is no ZONEID where there is a Z in the 6th position (i.e 01703Z0C does not exist). We need to insert a record with the exact same information except that the 6th position will be replaced with a Z instead of a Y. That is we will insert the following record

-1087634317,01703Z0C,MICHAEL,RUPP,Wilkes-Barre,PA,18701

For the 3rd last record above

-1833696950,01806Z5A,Raymond,Lackore,Va. Beach,VA,23454

There is a Z in the 6th position of the ZONEID field hence we leave the record as is

For the last two records

-1087634432,01703Y0C,Art,Heal,Scranton,PA,18510
-1087634432,01703Z0C,Art,Heal,Scranton,PA,18510

Both the records are identical except that we have a Y in the 6th position in one of the records and a Z in the other. Here also we leave the record as is. No action required.

So my requirement is to copy the whole record ( just change the 6th position in the ZONEID field) if ONLY one record for that ACCTID,ZONEID combination exists and the 6th position of the ZONEID field is a Y.

I want to check all the records in the table ( 2 Million records) and add a record if – for a particular ACCTID, ZONEID combination the 6th position of the ZONEID is Y only i.e no record exists for the same ACCTID where the 6th position of the ZONEID is Z.

Can this be done just by using SQL.

Thanks in advance. Received on Tue Oct 09 2001 - 14:04:17 CDT