Update table with the same table nested table

Hi,

I am using Oracle 11g.

I have customers table with the below object types as below

CREATE TYPE address_t AS OBJECT (
   street  VARCHAR2(30),
   city    VARCHAR2(20),
   state   CHAR(2),
   zip     CHAR(5) );
/
CREATE TYPE address_tab IS TABLE OF address_t;
/

CREATE TABLE customers (
   custid  NUMBER,
   modified_date date,
   description varchar2(2000),
   del_flag VARCHAR2(10),
   address address_tab )
NESTED TABLE address STORE AS customer_addresses;

INSERT INTO customers VALUES (1,sysdate-10,'abc','N'
            address_tab(
              address_t('101 First', 'Redwood Shores', 'CA', '94065')
            )  

INSERT INTO customers VALUES (2,sysdate-5,'pqr','N'
            address_tab(
              address_t('102 First', 'Redwood Shores', 'CA', '94065')
            )

INSERT INTO customers VALUES (3,sysdate,'mm//mm','N'
            address_tab(
              address_t('103 First', 'Redwood Shores', 'CA', '94065')
            )    

INSERT INTO customers VALUES (4,sysdate-3,'dddd','N'
            address_tab(
              address_t('102 First', 'Redwood Shores', 'CA', '84065')
            )

INSERT INTO customers VALUES (5,sysdate,''yy//mm'','N'
            address_tab(
              address_t('103 First', 'Redwood Shores', 'CA', '84065')
            )

INSERT INTO customers VALUES (6,sysdate,'aa//bb','N')


I need to develop a one time update script as below.

1) Update the "description" column of the customers table with the city and state values from the respective address column(object type)
   as city||'//'||state (in our ex. Redwood Shores//CA)

2) If zip code is same for multiple customers(in ex. 94065 is same for custid=1,2 and 3 customers then,
   update for the MAX modified_date customer(in ex. custid=3 customer) ie desctiption as 'Redwood Shores//CA'

3) Then the other 2 customers ( in ex. custid=1 and 3) mark the del_flag as 'Y' ( as modified date is not latest)

4) If value of address  column does not exists for any customer (in ex. custid=6) then replace the existing description ('//' with '~' )ie in ex. for cust 6 desacription shoud be 'aa~bb'

Can you please suggest how can I achieve this?

Thank you  in advance.
GouthamAnandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
looks like homework.  Can you post what you tried?
0
GouthamAnandAuthor Commented:
Hi,

My original tables are joining with some other tables having very huge data ( around 5 million records) in production database and taking more time

I tried something like below. But not sure if I can do  this in better way.

DECLARE

cursor c1 is
select custid,modified_date,description,del_flag,ad.city,ad.state,ad.zip      
from customers c, TABLE(address )(+) ad;

BEGIN

FOR rec in C1
LOOP

    IF rec.zip IS NOT NULL

      UPDATE customers c1
      SET description = rec.city||'//'||rec.state
        WHERE modified_date = (select max(modified_date)
                                       from customers c2,TABLE(address ) ad
                                       where ad.zip=rec.zip);

         UPDATE customers c1
       SET del_flag= 'Y'
       WHERE modified_date < (SELECT max(modified_date)
                                       FROM customers c2,TABLE(address ) ad
                                       WHERE ad.zip=rec.zip);
     
   ELSE
         UPDATE customers c1
      SET description = replace(description,'//','~')
        WHERE modified_date < (SELECT max(modified_date)
                                       FROM customers c2,TABLE(address ) ad
                                       WHERE ad.zip=rec.zip);
    END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
NULL;
END;

thank you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GouthamAnandAuthor Commented:
Did not get any response from experts. So asuming there is no better solution at this moment.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.