CODEWITHSUNDEEP
sqloracle-databaseplsql
Nvr
Nvr

Reputation: 171

Concanate data from one table to another

I want to concanate the data from TABLE1 to TABLE 2

TABLE 1

id        grp_name
-----------------------------
1       A@erf,[email protected]
2       [email protected],[email protected]/[email protected]
3       [email protected],[email protected],[email protected]
4       [email protected]#[email protected]
5       [email protected]

TABLE 2

code       name
-----------------------------------
1        [email protected],[email protected]
2        [email protected]
3        [email protected]
4        [email protected]
5        [email protected]
6        [email protected]

I want to join grp_name data with name data like concanate using comma (',') grp_name data having unwanted symboles like '#', '/', I want to elimate those too.

I created below procedure, but i dont know i effective or not. If it is possible with simple update statement alone or merge statement alone let me know.

Excepted result

code       name
1        [email protected],[email protected],A@erf,[email protected]
2        [email protected],[email protected],[email protected],[email protected]
3        [email protected],[email protected],[email protected],[email protected]
4        [email protected],[email protected],[email protected]
5        [email protected],[email protected]
6        [email protected]

CREATE OR REPLACE PROCEDURE procedure1
AS

    CURSOR cur
    IS
    SELECT id, grp_name
    FROM TABLE 1;
    
    CURSOR cur2
    IS
    SELECT code, name
    FROM TABLE 2;
    
      v_a       VARCHAR2(300);
 v_b       VARCHAR2(25);
 v_c    VARCHAR2(4000);
 v_d       VARCHAR2(250);

BEGIN

    FOR i IN cur
    LOOP
        v_a := ','||i.grp_name;
        v_b := i.id;
        
        FOR e IN cur2
        LOOP
            v_c := e.name || v_a ;
            v_d := i.code;
        
    
        UPDATE schema_name.TABLE 2
        SET name = v_c
        WHERE v_d = v_b;
        
        END LOOP;
    END LOOP;
    
--  COMMIT;
END;

Upvotes: 1

Views: 63

Answers (2)

KOBER
KOBER

Reputation: 59

Please try something like this:

With tab1 as
(
select 1 as id,'A@er#f,[email protected]' as grp_name from dual union all
select 2 as id,'[email protected],[email protected]#g/[email protected]' as grp_name from dual
),
tab2 as (
select 1 as id,'[email protected],[email protected]' as name from dual union all
select 2 as id,'[email protected]' as name from dual
)
select REGEXP_REPLACE (grp_name||','||name,'[^a-zA-Z0-9|\@|\,]','') as name from tab1 inner join tab2 using(id);

Result:

A@erf,R@erfin,A@weot,D@refas
B@goin,D@storgS@recuy,B@dein

Upvotes: 1

kfinity
kfinity

Reputation: 9091

In the simplest case, like @jarlh said, you can just do an update:

update table2
    set name = name 
            || (select ',' || regexp_replace(grp_name, '[/#]', ',') -- replace / and # with ,
                from table1
                where table1.id = table2.code)
where code in (select id from table1); -- only update matching rows

The last line is not strictly necessary, but it's good to avoid unnecessary updates.

If you also have rows in TABLE1 which don't have a match in TABLE2 that you want to add, use an insert:

insert into table2 (code, name)
    select id, regexp_replace(grp_name, '[/#]', ',')
    from table1
    where id not in (select code from table2);

Edit: and like Gordon mentioned, you would have an easier time with the SQL if you stored your data like this:

code       name
1        [email protected]
1        [email protected]
1        A@erf
1        [email protected]
2        [email protected]
2        [email protected]
2        [email protected]
2        [email protected]
3        [email protected]
3        [email protected]
3        [email protected]
3        [email protected]

Upvotes: 1

Related Questions