I have a table called CC_CLAIM and a config table. ID is the key column and the other column names are prefixed with V5_ & A5_
The V5 columns are filled with values but A5 columns are left blank so we need to update only A5 columns.
Table Name - CC_CLAIM:
Table_Name Column_Name Lookup_Table Lookup_Column
CC_CLAIM A5_CODE CCTL_LOBCODE TL_ID
CC_CLAIM A5_STATE CCTL_LOBSTATE TL_ID
CC_CLAIM A5_CLAIM CCTL_CLAIMPART TL_ID
The requirement is for each row in the CC_CLAIM table,
-- select the V5 column value (e.g: V5_CODE = 10001, which is already populated in table)
-- find the appropriate lookup table & lookup column in the config table (see bottom) for A5 column (A5_CODE, CCTL_LOBCODE, TL_ID)
-- find the value in lookup table using the known V5_CODE value, lookup_column (select TL_ID from CCTL_LOBCODE where ID = 10001)
-- update the result in CC_CLAIM table (update set A5_CODE = <result from above> WHERE ID = 111)
This has to be repeated for all the rows in CC_CLAIM table.
Please can you help me in writing script for this.