I have tables( are 3) that work as metadata, one of them stores the name of the table and the name of the columns that can be changed
I will place the script of the tables below , There are 3 tables :
XP_METADATA save table name and column name
XP_TAB01 save the ID of the table I want to make a change , usually a PK and others datas that are not relevant in the context
XP_TAB02 save information which columns, and values I want to change, the column is obtained through a foreign key with the table XP_METADATA
This only serves to store Update in some columns of 3 tables (XP_DATA01, XP_DATA02 and XP_DATA03) the scripts of these 3 tables with data will also be placed below
follow the script for creating these tables (METADATA) :
CREATE TABLE XP_METADATA
(
ID NUMBER,
TABLE_NAME VARCHAR2(30 BYTE),
COLUMN_NAME VARCHAR2(30 BYTE)
);
CREATE UNIQUE INDEX EXF_BAT_TRIB_METADADOS_PK ON XP_METADATA (ID);
ALTER TABLE XP_METADATA ADD (
CONSTRAINT XP_METADATA_PK
PRIMARY KEY
(ID)
USING INDEX EXF_BAT_TRIB_METADADOS_PK
ENABLE VALIDATE);
---
CREATE TABLE XP_TAB01
(
ID NUMBER,
CODE_SITE NUMBER(3),
CODE_SEQUENCE NUMBER(15),
L_STATUS VARCHAR2(1 BYTE) DEFAULT '0',
ACTION VARCHAR2(1 BYTE)
);
CREATE UNIQUE INDEX XP_TAB01_PK ON XP_TAB01(ID)LOGGING;
ALTER TABLE XP_TAB01 ADD (
CONSTRAINT XP_TAB01_PK
PRIMARY KEY
(ID)
USING INDEX XP_TAB01_PK
ENABLE VALIDATE);
---
CREATE TABLE XP_TAB02
(
ID NUMBER,
ID_XP01 NUMBER,
SEQ_NUM NUMBER(6),
NUM_PARC NUMBER(3),
ID_XP_METADATA NUMBER,
VALOR_CALCULADO VARCHAR2(4000 BYTE)
);
CREATE UNIQUE INDEX XP_TAB02_PK ON XP_TAB02
(ID)
LOGGING;
CREATE INDEX XP_TAB02_IDX01 ON XP_TAB02
(ID_XP_METADATA)
LOGGING;
CREATE INDEX XP_TAB02_IDX02 ON XP_TAB02
(ID_XP01)
LOGGING;
ALTER TABLE XP_TAB02 ADD (
CONSTRAINT XP_TAB02_PK
PRIMARY KEY
(ID)
USING INDEX XP_TAB02 _PK
ENABLE VALIDATE);
ALTER TABLE XP_TAB02 ADD (
CONSTRAINT XP_TAB02_R01
FOREIGN KEY (ID_XP_METADATA)
REFERENCES XP_METADATA (ID)
ENABLE VALIDATE,
CONSTRAINT XP_TAB02_R02
FOREIGN KEY (ID_XP01)
REFERENCES XP_TAB01 (ID)
ON DELETE CASCADE
ENABLE VALIDATE);
Now follows the script of the 3 tables that will be monitored
CREATE TABLE XP_DATA01
(
CODE_SITE NUMBER(3),
CODE_SEQUENCE NUMBER(15),
N_VALUE01 NUMBER,
N_VALUE02 NUMBER,
C_VALUE03 VARCHAR2(300 BYTE)
);
CREATE TABLE XP_DATA02
(
ID NUMBER(21),
CODE_SITE NUMBER(3),
CODE_SEQUENCE NUMBER(15),
SEQ_NUM NUMBER(6),
T02_VALUE01 NUMBER,
T02_VALUE02 NUMBER,
T02_VALUE03 NUMBER,
T02_VALUE04 NUMBER,
T02_VALUE05 NUMBER,
T02_VALUE06 NUMBER,
C02_VALUE01 VARCHAR2(32 BYTE),
C02_VALUE02 VARCHAR2(32 BYTE),
C02_VALUE03 VARCHAR2(32 BYTE),
C02_VALUE04 VARCHAR2(32 BYTE),
C02_VALUE05 VARCHAR2(32 BYTE),
C02_VALUE06 VARCHAR2(32 BYTE)
);
CREATE TABLE XP_DATA03
(
ID NUMBER(21),
CODE_SITE NUMBER(3),
CODE_SEQUENCE NUMBER(15),
NUM_PARC NUMBER(6),
T02_VALUE01 NUMBER,
T02_VALUE02 NUMBER,
T02_VALUE03 NUMBER,
T02_VALUE04 NUMBER,
T02_VALUE05 NUMBER,
T02_VALUE06 NUMBER,
C02_VALUE01 VARCHAR2(32 BYTE),
C02_VALUE02 VARCHAR2(32 BYTE),
C02_VALUE03 VARCHAR2(32 BYTE),
C02_VALUE04 VARCHAR2(32 BYTE),
C02_VALUE05 VARCHAR2(32 BYTE),
C02_VALUE06 VARCHAR2(32 BYTE)
);
Follow the insertion of some data in these 6 tables
Insert into XP_DATA01(CODE_SITE, CODE_SEQUENCE, N_VALUE01, N_VALUE02, C_VALUE03) Values(1, 1000, 1000, 1500, '2000');
Insert into XP_DATA01(CODE_SITE, CODE_SEQUENCE, N_VALUE01, N_VALUE02, C_VALUE03) Values(1, 2001, 800, 400, 'XPTO');
Insert into XP_DATA01(CODE_SITE, CODE_SEQUENCE, N_VALUE01, N_VALUE02, C_VALUE03) Values(1, 1001, 600, 300, 'ABCD');
Insert into XP_DATA01(CODE_SITE, CODE_SEQUENCE, N_VALUE01, N_VALUE02, C_VALUE03) Values(1, 2002, 300, 150, '6352');
COMMIT;
Insert into XP_DATA02(ID, CODE_SITE, CODE_SEQUENCE, SEQ_NUM, T02_VALUE01,T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06,C02_VALUE01, C02_VALUE02, C02_VALUE03, C02_VALUE04, C02_VALUE05,C02_VALUE06)
Values (1, 1, 1000, 1, 1000,900, 800, 700, 600, 500,'400', '300', '200A', '200C', '300D','400D');
Insert into XP_DATA02 (ID, CODE_SITE, CODE_SEQUENCE, SEQ_NUM, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06, C02_VALUE01, C02_VALUE02, C02_VALUE03, C02_VALUE04, C02_VALUE05, C02_VALUE06)
Values (2, 1, 1000, 2, 2000, 800, 700, 600, 500, 400, '40A', '30B', '20C', '20D', '10D', '10D');
Insert into XP_DATA02 (ID, CODE_SITE, CODE_SEQUENCE, SEQ_NUM, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06, C02_VALUE01, C02_VALUE02, C02_VALUE03, C02_VALUE04, C02_VALUE05, C02_VALUE06)
Values (3, 1, 1000, 3, 3000, 700, 600, 500, 400, 300, '30B', '30C', '30D', '30E30', '30F', '40D');
Insert into XP_DATA02 (ID, CODE_SITE, CODE_SEQUENCE, SEQ_NUM, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06, C02_VALUE01, C02_VALUE02, C02_VALUE03, C02_VALUE04, C02_VALUE05, C02_VALUE06)
Values (4, 1, 1000, 4, 4000, 600, 500, 400, 300, 200, 'ABCD', 'EFGH', 'IJK', '20A', '2B', '20C');
Insert into XP_DATA02 (ID, CODE_SITE, CODE_SEQUENCE, SEQ_NUM, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06, C02_VALUE01, C02_VALUE02, C02_VALUE03, C02_VALUE04, C02_VALUE05, C02_VALUE06)
Values (5, 1, 2001, 1, 1000, 2000, 3000, 4000, 5000, 6000, '7000', '8000', 'ABCD', '123', '456', '789');
Insert into XP_DATA02 (ID, CODE_SITE, CODE_SEQUENCE, SEQ_NUM, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06, C02_VALUE01, C02_VALUE02, C02_VALUE03, C02_VALUE04, C02_VALUE05, C02_VALUE06)
Values (6, 1, 1001, 1, 1000, 3500, 200, 100, 50, 30, '111', '222', '333', '444', '555', '666');
Insert into XP_DATA02 (ID, CODE_SITE, CODE_SEQUENCE, SEQ_NUM, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06, C02_VALUE01, C02_VALUE02, C02_VALUE03, C02_VALUE04, C02_VALUE05, C02_VALUE06)
Values (7, 1, 2002, 1, 100, 200, 300, 400, 500, 600, '700', '800', '900', '1000', '1001', '1002');
Insert into XP_DATA02 (ID, CODE_SITE, CODE_SEQUENCE, SEQ_NUM, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06, C02_VALUE01, C02_VALUE02, C02_VALUE03, C02_VALUE04, C02_VALUE05, C02_VALUE06)
Values (8, 1, 2002, 2, 200, 300, 400, 500, 600, 700, '800', '900', '200', '300', '400', '500');
COMMIT;
------
Insert into XP_DATA03 (ID, CODE_SITE, CODE_SEQUENCE, NUM_PARC, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06)
Values (1, 1, 1000, 1, 100, 1005, 101, 1015, 102, 1025);
Insert into XP_DATA03 (ID, CODE_SITE, CODE_SEQUENCE, NUM_PARC, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06)
Values (2, 1, 1000, 2, 2000, 20005, 2001, 20015, 2002, 20025);
Insert into XP_DATA03 (ID, CODE_SITE, CODE_SEQUENCE, NUM_PARC, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06)
Values (3, 1, 2002, 1, 100, 1005, 101, 1015, 102, 1025);
Insert into XP_DATA03 (ID, CODE_SITE, CODE_SEQUENCE, NUM_PARC, T02_VALUE01, T02_VALUE02, T02_VALUE03, T02_VALUE04, T02_VALUE05, T02_VALUE06)
Values (4, 1, 2002, 2, 200, 2005, 201, 2015, 202, 2025);
COMMIT;
------
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (1, 'XP_DATA01', 'N_VALUE01 ');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (2, 'XP_DATA01', 'N_VALUE02 ');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (3, 'XP_DATA01', 'C_VALUE03 ');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (4, 'XP_DATA02', 'T02_VALUE01');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (5, 'XP_DATA02', 'T02_VALUE02');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (6, 'XP_DATA02', 'T02_VALUE03');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (7, 'XP_DATA02', 'T02_VALUE04');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (8, 'XP_DATA02', 'T02_VALUE05');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (9, 'XP_DATA02', 'T02_VALUE06');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (10, 'XP_DATA02', 'C02_VALUE01');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (11, 'XP_DATA02', 'C02_VALUE02');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (12, 'XP_DATA02', 'C02_VALUE03');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (13, 'XP_DATA02', 'C02_VALUE04');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (14, 'XP_DATA02', 'C02_VALUE05');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (15, 'XP_DATA02', 'C02_VALUE06');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (16, 'XP_DATA03', 'T02_VALUE01');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (17, 'XP_DATA03', 'T02_VALUE02');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (18, 'XP_DATA03', 'T02_VALUE03');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (19, 'XP_DATA03', 'T02_VALUE04');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (20, 'XP_DATA03', 'T02_VALUE05');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (21, 'XP_DATA03', 'T02_VALUE06');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (22, 'XP_DATA03', 'C02_VALUE01');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (23, 'XP_DATA03', 'C02_VALUE02');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (24, 'XP_DATA03', 'C02_VALUE03');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (25, 'XP_DATA03', 'C02_VALUE04');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (26, 'XP_DATA03', 'C02_VALUE05');
Insert into XP_METADATA (ID, TABLE_NAME, COLUMN_NAME) Values (27, 'XP_DATA03', 'C02_VALUE06');
COMMIT;
-----
Insert into XP_TAB01 (ID, CODE_SITE, CODE_SEQUENCE, L_STATUS) Values (1, 1, 1000, '0');
Insert into XP_TAB01 (ID, CODE_SITE, CODE_SEQUENCE, L_STATUS) Values (2, 1, 2002, '0');
COMMIT;
----
Insert into XP_TAB02 (ID, ID_XP01, ID_XP_METADATA, VALOR_CALCULADO) Values (1, 1, 1, '1500');
Insert into XP_TAB02 (ID, ID_XP01, SEQ_NUM, ID_XP_METADATA, VALOR_CALCULADO) Values (2, 1, 1, 4, '1001');
Insert into XP_TAB02 (ID, ID_XP01, SEQ_NUM, ID_XP_METADATA, VALOR_CALCULADO) Values (3, 1, 1, 5, '950.55');
Insert into XP_TAB02 (ID, ID_XP01, NUM_PARC, ID_XP_METADATA, VALOR_CALCULADO) Values (4, 1, 1, 16, '100.20');
Insert into XP_TAB02 (ID, ID_XP01, NUM_PARC, ID_XP_METADATA, VALOR_CALCULADO) Values (5, 1, 1, 17, '1006');
Insert into XP_TAB02 (ID, ID_XP01, NUM_PARC, ID_XP_METADATA, VALOR_CALCULADO) Values (7, 1, 2, 16, '200');
Insert into XP_TAB02 (ID, ID_XP01, NUM_PARC, ID_XP_METADATA, VALOR_CALCULADO) Values (8, 2, 1, 21, '3000');
COMMIT;
I am looking to make a dynamic sql to read data from the tables (XP_METADATA, XP_TAB01, XP_TAB02) and UPDATES the tables (XP_DATA01, XP_DATA02, XP_DATA03)
I tried a first version of the update as below :
SELECT 'UPDATE '||C.TABLE_NAME||' SET '|| C.COLUMN_NAME ||' = '||B.VALOR_CALCULADO || ' WHERE CODE_SITE = '||A.CODE_SITE ||' AND DOF_SEQUENCE = '|| A.CODE_SEQUENCE ||
DECODE(NVL(B.SEQ_NUM,-1),-1,DECODE(NVL(B.NUM_PARC,-1),-1,'',' NUM_PARC = '|| B.NUM_PARC),' SEQ_NUM = '||B.SEQ_NUM)|| ';' COLUPDATE
FROM XP_TAB01 A,
XP_TAB02 B,
XP_METADATA C
WHERE A.ID = B.ID_XP01
AND B.ID_XP_METADATA = C.ID;
UPDATE XP_DATA01 SET N_VALUE01 = 1500 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000;
UPDATE XP_DATA02 SET T02_VALUE01 = 1001 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 SEQ_NUM = 1;
UPDATE XP_DATA02 SET T02_VALUE02 = 950.55 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 SEQ_NUM = 1;
UPDATE XP_DATA03 SET T02_VALUE01 = 100.20 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 NUM_PARC = 1;
UPDATE XP_DATA03 SET T02_VALUE02 = 1006 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 NUM_PARC = 1;
UPDATE XP_DATA03 SET T02_VALUE01 = 200 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 NUM_PARC = 2;
UPDATE XP_DATA03 SET T02_VALUE06 = 3000 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 2002 NUM_PARC = 1;
but I would like to do it in a way that for the same table and the same row, he would do a single , for example
in
UPDATE XP_DATA02 SET T02_VALUE01 = 1001 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 SEQ_NUM = 1;
UPDATE XP_DATA02 SET T02_VALUE02 = 950.55 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 SEQ_NUM = 1;
I would like
UPDATE XP_DATA02 SET T02_VALUE01 = 1001 , T02_VALUE02 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 SEQ_NUM = 1;
other example
UPDATE XP_DATA03 SET T02_VALUE01 = 100.20 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 NUM_PARC = 1;
UPDATE XP_DATA03 SET T02_VALUE02 = 1006 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 NUM_PARC = 1;
I would like
UPDATE XP_DATA03 SET T02_VALUE01 = 100.20 , T02_VALUE02 = 1006 WHERE CODE_SITE = 1 AND DOF_SEQUENCE = 1000 NUM_PARC = 1;
Is there a way to merge the updates into a single command?
using
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production