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.
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.
looks like homework. Can you post what you tried?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Did not get any response from experts. So asuming there is no better solution at this moment.