Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1622
  • Last Modified:

Split values of data column in Oracle db table

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?

  • 2
  • 2
  • 2
  • +1
1 Solution
slightwv (䄆 Netminder) Commented:
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

GouthamAnandAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
>> (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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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?
Alexander Eßer [Alex140181]Software DeveloperCommented:
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

GouthamAnandAuthor Commented:
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.
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now