Split values of data column in Oracle db table

Posted on 2013-06-25
Last Modified: 2013-06-26
I am using Oracle 11g database.

I have two tables table1 with columns A ,B, Flag and table2 with columns C,D, flag
I have data in table1 as

A     B               flag
---    ---             ---
1    10,20          Y

ie coulumn B is having comma separated values (one or any number of values)

In table2 I have data as

C     D      flag
---   ----
1    20      Y
1    30     Y

Now I need to merge the data of table1 into table2 using matching of table1.A=table2.C and comma separated values of table 1.B with the values of table2.D column
and need to update the table2 data as

C    D   Flag
---  ---   -----
1   10  Y       --Newly inserted row
1   20 Y       -- Update other columns of this row
1   30 N      -- Update Flage to 'N' as 30 is not present in table1.B column for value 1 of column A

Can you please suggest how can I achieve this?

Question by:GouthamAnand
  • 2
  • 2
  • 2
  • +1
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39274508
Try this:

drop table tab1 purge;
create table tab1(a number, b varchar2(10), flag char(1));

insert into tab1 values(1,'10,20','Y');

drop table tab2 purge;
create table tab2(c number, d number, flag char(1));

insert into tab2 values(1,20,'Y');
insert into tab2 values(1,30,'Y');

select a,mynum, max(flag) flag
	select a, to_number(rtrim(regexp_substr(b,'([[:alnum:]]*)(,)?',1,column_value),',')) mynum, flag
	from tab1,
  	multiset(select level from dual connect by level <= ((length(b)-length(replace(b,','))))+1)
  	as sys.odcinumberlist
select c,d,'N' from tab2
group by a, mynum

Open in new window


Author Comment

ID: 39275258
Thank you very much. I will try this and let you know the result.

But I think this is "selecting" the values as per my requirement.

But I have to "Update" the table tab2 when column values of C and D match with A and B(after splitting the comma separated values). In my ex.  1 and 20 values.

I have some other columns in table1 and some other columns in table2. which need to be matched and "updated in table2".

ie I have other columns which needs to be updated along with flag column in table2 based on the values of table1.

Also I need to "Insert" in the table tab2 when they do not exits .(in my ex. they are 1 and 10 along with other coulumns).

Then again I need to update "only flag column" where the record value for column d does not exists in the comma separated values.

In my example its 1 and 30. ie I am doing "logical delete of that record". But not physically deleting.

As I mentioned in my question
C    D   Flag
---  ---   -----
1   10  Y       -- should  get inserted in table2
1   20 Y       -- should update the other columns of this row.As flag is anyway same.
1   30 N      -- sholud update Flag to 'N' .( as 30 is not present in table1.B column for value       1 of column A). Here I do not touch other columns. Because its logical delete.

Request you to suggest for the Update and Insert (kind of Merge statement) of the table2 as per the above requirement.

Thank you for your kind help in advance.
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39275277
>> (kind of Merge statement)

Might I suggest the MERGE command?

If you cannot figure it out, just post back and I'll see if I can mock up a test case.  MERGE is pretty straight forward.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 32

Accepted Solution

awking00 earned 500 total points
ID: 39275581
I've been playing with the merge and the following appears to be working:
merge into tab2 t2
 (select t.a ,trim(x.column_value.extract('e/text()')) b, flag
 from tab1 t,
 table (xmlsequence(xmltype('<e><e>' || replace(t.b,',','</e><e>')||  '</e></e>').extract('e/e'))) x
 union all
 select tab2.c, to_char(tab2.d) d, 'N' flag
 from tab2
 where not exists
 (select 1 from tab1 where tab1.a = tab2.c and instr(tab1.b,tab2.d) > 0)
 ) t1
on (t1.a = t2.c and t1.b = t2.d)
when matched then
update set t2.flag = t1.flag
when not matched then
insert values
(t1.a, t1.b, t1.flag);
It doesn't take into account the other columns, but should be easily modified. Can you provide examples of the tables with the other columns (1 or 2 should suffice) with your expected result?
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39275961
I know you want to use the MERGE, but maybe this one helps, too ;-)

with sel as
 (select trim(regexp_substr(str, '[^ '']+', 1, level)) as val_col,
         a as id_col,
    from (select rownum as id,
                 trim(regexp_replace(a.b, ',+', ' ')) str,
            from tab1 a)
  connect by instr(str, ' ', 1, level - 1) > 0
         and id = prior id
         and prior dbms_random.value is not null)
select b.c id_col,
       to_char(b.d) val_col,
       'N' flag
  from tab2 b
 where not exists (select 'X'
          from sel
         where val_col = to_char(b.d))
select id_col,
  from sel;

Open in new window


Author Closing Comment

ID: 39276948
Thank you very much.

This is perfectly suites my requirement.

This addressed both the problems which I am facing while using  merge statement. ie
1) Splitting the values in table1 column.
2) Updating table2 values which are "not there in table1 split values."
( as normal merge update only matching values)

As you said I can easily modify the statement accordingly to add the other columns while update and insert statement of merge.

Thanks and Regards.
LVL 32

Expert Comment

ID: 39278041
Glad to help. Just be sure to update the flag field to satisfy the requirement when the record doesn't exist in the other table along with the other columns, even though you will frequently be updating the flag from 'Y' to "Y' when it does.

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query to identify changes between rows of two tables 8 46
oracle 11g 23 78
SQL Developer 6 48
Component is listed with a Protocol more than once 3 28
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question