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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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!
0
ralph_rea
Asked:
ralph_rea
  • 7
  • 3
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now