Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    19

    Unanswered: Update using Dynamic SQL and generated column names

    Hi,
    I have a cursor that produces a large amount of data with the below metadata -

    Code:
    KEY_COL	TGT_COL	TGT_VAL
    The objective is to produce a dynamic update statement like this -
    Code:
    UPDATE ABC_TABLE
    SET TGT_COL=TGT_VAL
    WHERE Key=KEY_COL;
    The TGT_COL could be any of the 50 columns in the ABC_TABLE.

    Currently, I'm looping through the cursor -

    Code:
    FOR i IN cur_Dynamic
    LOOP
    vSql:='UPDATE ABC_TABLE SET '||i.TGT_COL||'='||CHR(39)||i.TGT_VAL||CHR(39)||' WHERE Key='||i.KEY_COL;
    EXECUTE IMMEDIATE vSql;
    END LOOP;
    But this is taking a very long time to execute. Is there a way I can better code this to make it run faster?

  2. #2
    Join Date
    Oct 2007
    Posts
    52
    Provided Answers: 3
    since you are running a cursor to determine the values to be put into this update statement, why not just write an update statement that performs the update with the required values? Something along the lines of:

    update tablex
    set columny = (select new_value from wherever where these_conditions_apply)
    where???

  3. #3
    Join Date
    Sep 2016
    Location
    Pune
    Posts
    16
    Hii,
    I am newbie here As per my knowledge You can pass the name of the column in dynamic sql:

    declare @sql nvarchar (1000);
    set @sql = N'update table set ' + @column_name + '= ''''';

    exec sp_executesql @sql;

    *If u know more please guide me ..Thank You !!

  4. #4
    Join Date
    Oct 2007
    Posts
    52
    Provided Answers: 3
    should really start your own thread for a new question and the OP on this one already gave you a sample above.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    @DNance: I think that snehasingh just wanted to help as you do. Although, he should take care of the forum name, as posting non-Oracle (sqlserver?) code in Oracle forum space does not make much sense.

    As the column names are present in the variable, they cannot be statically stated in the UPDATE statement.
    And as the main performance problem except of the whole process concept (no use of binding, key responses marked as helpful) was already spotted in this thread: https://community.oracle.com/thread/3971805 without any further feedback, I see no reason in adding anything.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •