All,
Ive 2 below tables: Workers table contains CODE (PK) and NAME column and Archives table as secondary to be updated.
I want to use the Worker table as stated below with same records to update Archives table and populate MEMBERS column as ( 11:15 ==> James:Mercy) i.e. MEMBERS column with have corresponding NAMES as colon delimited
TABLE=workers
code names
============
11 James
13 Peter
15 Mercy
17 Prome
**TABLE = Archives**
Codes Members
================
11:15
15
13
15:11:17
so far ive tried ny below query but doesnt work:
UPDATE Archives R SET Members = (
SELECT LISTAGG(names,':') WITHIN GROUP (ORDER BY names) "MEMBERS" FROM workers WHERE code IN (
with rws as (
select Codes str from dual
)
select LPAD( trim( regexp_substr (
str,
'[^:]+',
1,
level
) ),2,'0') value
from rws
connect by level <=
length ( str ) - length ( replace ( str, ':' ) ) + 1
) )
;
any help is appreciated.