Link to home
Start Free TrialLog in
Avatar of GouthamAnand
GouthamAnand

asked on

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.
Avatar of Sean Stuber
Sean Stuber

looks like homework.  Can you post what you tried?
ASKER CERTIFIED SOLUTION
Avatar of GouthamAnand
GouthamAnand

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GouthamAnand

ASKER

Did not get any response from experts. So asuming there is no better solution at this moment.