Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Split values of data column in Oracle db table

Posted on 2013-06-25
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 77

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 77

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.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 32

Accepted Solution

awking00 earned 2000 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 14

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

609 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