I have a scenario where i need to update the value of a column or say append !
Below is test table and data
create table test (rsrc_nm varchar2(50), parm varchar2(400));
insert into test values ('HLRCamelProfileBasic','resource_code:7|resource_type:HLR_Camel_Profile|Priority:1|');
insert into test values ('HSSUSERProfileBasic','resource_code:3|resource_type:HSS_User_Profile|Priority:1|');
insert into test values ('HSSUSERProfileBasic','resource_code:3|resource_type:HSS_User_Profile|Priority:2|');
insert into test values ('HSSUSERProfileBasic','resource_code:3|resource_type:HSS_User_Profile|Priority:1|');
insert into test values ('HLRBaseProfileBasic','resource_code:1|resource_type:HLR_Base_Profile|Priority:2|');
insert into test values ('HLRBaseProfileBasic','resource_code:1|resource_type:HLR_Base_Profile|Priority:3|');
Here we have 2 columns of a staging table in which rsrc_nm and parm are given what i have to do is i need to update or append the rsrc_nm column for parm coulmn where the last value i.e. priority is changing from 1 to 2 for same rsrc_nm.
For example
rsrc_ nm -- HSSUSERProfileBasic parm -- resource_code:3|resource_type:HSS_User_Profile|Priority:1|
rsrc_ nm -- HSSUSERProfileBasic parm -- resource_code:3|resource_type:HSS_User_Profile|Priority:2|
Here we have same parm priority but the values are different and i need to insert these values into another table for which i need to separate all PIPE values so while i insert it in the table it gives me error for unique constraint because that table table have 3 columns
RSRC_NM PARM VAL
------------------------------- -------------- ----------------
HSSUSERProfileBasic Priority 1
HLRCamelProfileBasic Priority 1
HLRBaseProfileBasic Priority 2
And on this table we have primary key on first two columns which enforce unique constraint also so i can not insert " HLRCamelProfileBasic " rsrc_nm for parm " Priority " because it fails in uniqueness.
So i find a solution to overcome this if i can append or update the rsrc_nm HLRCamelProfileBasic to " HLRCamelProfileBasic_1 " for " Priority:1 " and HLRCamelProfileBasic_2 for " Priority:2 " and so on for all RSRC_NM in Staging table.
MY DB is -- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Aucun commentaire:
Enregistrer un commentaire