Dynamic SQL UPDATE

Hi

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:

ID NUMBER,
V5_CODE NUMBER,
A5_CODE NUMBER,
V5_CLAIMID NUMBER,
A5_CLAIMID NUMBER,
V5_STATE NUMBER,
A5_STATE NUMBER

Config table:
==========
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.
Subhashini ElangoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
I'm not sure I fully understand what you are trying to do.  However, this looks pretty close to what you are asking for (at least to me):
UPDATE cc_claim a 
SET    a5_code = (SELECT tl_id 
                  FROM   cctl_lobcode b 
                  WHERE  a.v5_code = b.id), 
       a5_claimid = (SELECT tl_id 
                     FROM   cctl_lobstate c 
                     WHERE  a.v5_claim_id = c.id), 
       a5_state = (SELECT tl_id 
                   FROM   cctl_claimpart d 
                   WHERE  a.v5_state = c.id) 
WHERE  a.a5_code IS NULL; 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subhashini ElangoAuthor Commented:
Hi Johnson,

Many thanks for the reply.

I think I was not clear in my question so I will explain bit further.

Your update statement is absolutely correct but I want to find the name of the lookup_table & lookup_column for each & every A5 column in a row and then update.

This means,

-- Select the V5 column value (e.g: V5_CODE = 10001, which is already populated in table and known to us)

-- Go the config table and find the appropriate lookup table & lookup column for each A5 column (For A5_CODE the lookup table is CCTL_LOBCODE and lookup_column is TL_ID and note that lookup table is differ for each column)

 -- Once the lookup table is identified, find the value using the lookup_column (select TL_ID from CCTL_LOBCODE where ID = 10001)

 -- Now update the result in CC_CLAIM table with the above result (update set A5_CODE = TL_ID WHERE ID = 111)

There are various other tables so I would need all the above steps to be dynamic.

Appreciate your time, Thanks.
0
johnsoneSenior Oracle DBACommented:
Assuming that your config table looks like this:
CREATE TABLE config_table 
  ( 
     table_name    VARCHAR2(50), 
     column_name   VARCHAR2(50), 
     lookup_table  VARCHAR2(50), 
     lookup_column VARCHAR2(50) 
  ); 

INSERT INTO config_table 
VALUES      ('CC_CLAIM', 
             'A5_CODE', 
             'CCTL_LOBCODE', 
             'TL_ID'); 

INSERT INTO config_table 
VALUES      ('CC_CLAIM', 
             'A5_STATE', 
             'CCTL_LOBSTATE', 
             'TL_ID'); 

INSERT INTO config_table 
VALUES      ('CC_CLAIM', 
             'A5_CLAIM', 
             'CCTL_CLAIMPART', 
             'TL_ID'); 

COMMIT; 

Open in new window

Then this query would build the update statements:
SELECT 'update ' 
       || table_name 
       || ' a set ' 
       || column_name 
       || ' = (select ' 
       || lookup_column 
       || ' from ' 
       || lookup_table 
       || ' b  where a.v' 
       || Substr(column_name, 2) 
       || ' = b.id);' 
FROM   config_table; 

Open in new window

There are a multitude of ways that you can run this.  Two of the most popular that I see are:
Spool the statements to a file and run the file
PL/SQL block (or stored procedure) that selects the statements and runs them with EXECUTE IMMEDIATE

Just be aware this is a very inefficient way to do what you are asking.
0
johnsoneSenior Oracle DBACommented:
Sorry, the update statements generated by that select would be:
update CC_CLAIM a set A5_CODE = (select TL_ID from CCTL_LOBCODE b where a.v5_CODE = b.id);
update CC_CLAIM a set A5_STATE = (select TL_ID from CCTL_LOBSTATE b where a.v5_STATE = b.id);
update CC_CLAIM a set A5_CLAIM = (select TL_ID from CCTL_CLAIMPART b where a.v5_CLAIM = b.id);

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.