mercredi 15 juin 2016

Update column value after some validation on it

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