Oracle Update

Hi,
I have these tables:
CREATE TABLE TAB_RM
(
  L_ID                VARCHAR2(500 BYTE)       DEFAULT NULL,
  B_ID                VARCHAR2(12 BYTE)        DEFAULT NULL                  NOT NULL,
  F_ID                VARCHAR2(4 BYTE)         DEFAULT NULL                  NOT NULL,
  R_ID                VARCHAR2(8 BYTE)         DEFAULT NULL                  NOT NULL
);
CREATE TABLE TAB_GV
(
  L_ID                VARCHAR2(500 BYTE)       DEFAULT NULL,
  B_ID                VARCHAR2(12 BYTE)        DEFAULT NULL                  NOT NULL,
  F_ID                VARCHAR2(4 BYTE)         DEFAULT NULL                  NOT NULL,
  R_ID                VARCHAR2(8 BYTE)         DEFAULT NULL                  NOT NULL
);
ALTER TABLE TAB_RM ADD (
  CONSTRAINT TAB_RM_PK
 PRIMARY KEY
 (B_ID, F_ID, R_ID)
 );
 ALTER TABLE TAB_GV ADD (
  PRIMARY KEY
 (B_ID, F_ID, R_ID, L_ID)
 );                     
ALTER TABLE TAB_GV ADD (
  CONSTRAINT FK_GV_FL_BL_RM
 FOREIGN KEY (B_ID, F_ID, R_ID)
 REFERENCES TAB_RM (B_ID,F_ID,R_ID)
    ON DELETE SET NULL);
      
Insert into TAB_RM (L_ID, B_ID, F_ID, R_ID) Values (NULL, 'F011', 'P1', '001');
Insert into TAB_RM (L_ID, B_ID, F_ID, R_ID) Values (NULL, 'F015', 'P1', '001');
Insert into TAB_RM (L_ID, B_ID, F_ID, R_ID) Values (NULL, 'T022', 'T1', '003');
Insert into TAB_RM (L_ID, B_ID, F_ID, R_ID) Values (NULL, 'PT03', 'P2', '006');

Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values  ('000000002', 'F011', 'P1', '001');
Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values  ('000000132', 'F011', 'P1', '001');  
Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values ('000002234', 'F011', 'P1', '001');  
Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values ('000023467', 'F011', 'P1', '001');
   
Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values ('001234598', 'F015', 'P1', '001');  
   
Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values ('000066666', 'T022', 'T1', '003');  
Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values ('000067890', 'T022', 'T1', '003');
   
Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values ('000000007', 'PT03', 'P2', '006');  
Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values ('000000008', 'PT03', 'P2', '006');  
Insert into TAB_GV (L_ID, B_ID, F_ID, R_ID) Values ('000000009', 'PT03', 'P2', '006');

I'd like to update the column TAB_RM.L_ID with the values of the L_ID in the table TAB_CV on equal B_ID+F_ID+R_ID separated by semicolons.

In my case I'd like to get this output:

Insert into TAB_RM (L_ID, B_ID, F_ID, R_ID) Values ('000000002;000000132;000002234;000023467', 'F011', 'P1', '001');
Insert into TAB_RM (L_ID, B_ID, F_ID, R_ID) Values ('001234598', 'F015', 'P1', '001');
Insert into TAB_RM (L_ID, B_ID, F_ID, R_ID) Values ('000066666;000067890', 'T022', 'T1', '003');
Insert into TAB_RM (L_ID, B_ID, F_ID, R_ID) Values ('000000007;000000008;000000009', 'PT03', 'P2', '006');
COMMIT;

How can I write this update?

Thanks in advance!
ralph_reaAsked:
Who is Participating?
 
sdstuberCommented:
then use the xmlagg method, alternately you do use 10g COLLECT function and tbl2str function (search EE for examples/source code)



UPDATE tab_rm rm
   SET l_id      =
           (SELECT tbl2str(CAST(COLLECT(gv.l_id ORDER BY gv.l_id) AS vcarray),';')
              FROM tab_gv gv
             WHERE gv.b_id = rm.b_id AND gv.f_id = rm.f_id AND gv.r_id = rm.r_id)
 WHERE rm.l_id IS NULL
0
 
sdstuberCommented:
for output you want 4 insert statements and a commit?

or, you simply want those 4 rows to be inserted into your tab_rm table?
0
 
ralph_reaAuthor Commented:
I want UPDATE only column L_ID of the TAB_RM column:

UPDATE TAB_RM
SET L_ID = (...................)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
PDATE tab_rm rm
   SET l_id      =
           (SELECT listagg(l_id,';') within group (order by l_id)
              FROM tab_gv gv
             WHERE gv.b_id = rm.b_id AND gv.f_id = rm.f_id AND gv.r_id = rm.r_id)
 WHERE rm.l_id IS NULL
0
 
sdstuberCommented:
if you don't have 11gR2 you won't have listagg function,  you can create your own stragg  (search EE for lots of examples)
or you can use xml aggregation (I know, the data isn't xml, it's just a syntax technique)

UPDATE tab_rm rm
   SET l_id      =
           (SELECT RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", gv.l_id || ';') order by gv.l_id), '/x/text()').getstringval(),';')
              FROM tab_gv gv
             WHERE gv.b_id = rm.b_id AND gv.f_id = rm.f_id AND gv.r_id = rm.r_id)
 WHERE rm.l_id IS NULL
0
 
ralph_reaAuthor Commented:
My version is: Oracle 10g
0
 
ralph_reaAuthor Commented:
Could I use the SYS_CONNECT_BY_PATH Operator??
0
 
sdstuberCommented:
yes,  but that is the most complicated and least efficient way of doing string aggregation.

if you don't want to/aren't allowed to create types/functions then use the xmlagg method above,
 it's MUCH more efficient than the connect by method
0
 
sdstuberCommented:
on second thought, you might not be able to do the connect by method because it requires an inline view and the predicate pushing of the tab_rm/tab_gv conditions won't be allowed

but, again, it's not an efficient method anyway
0
 
sdstuberCommented:
ok, on third thought,  it is possible,  but again,  I don't recommend this...

I'm posting it only for academic purposes

UPDATE tab_rm rm
   SET l_id      =
           (SELECT     MAX(SUBSTR(SYS_CONNECT_BY_PATH(l_id, ';'), 2))
                  FROM (SELECT l_id,
                               b_id,
                               f_id,
                               r_id,
                               ROW_NUMBER() OVER (PARTITION BY b_id, f_id, r_id ORDER BY l_id) curr,
                               ROW_NUMBER() OVER (PARTITION BY b_id, f_id, r_id ORDER BY l_id) - 1 prev
                          FROM tab_gv) gv
                 WHERE gv.b_id = rm.b_id AND gv.f_id = rm.f_id AND gv.r_id = rm.r_id
            START WITH curr = 1
            CONNECT BY PRIOR curr = prev)
 WHERE rm.l_id IS NULL;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.