1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Update Multiple Columns...

Discussion in 'SQL PL/SQL' started by patelsameerm, Oct 12, 2010.

  1. patelsameerm

    patelsameerm Guest

    Hi Folks,

    I need your help in resolving one issue in database.

    Here is the situation:

    I have a 'REG_SCHEDULE' table looks like as below.


    SCHEDULE_INDEX COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8
    1 GH MK 13 PW WI BL MY AN
    2 MK RR JW ER KS CP AW RT
    3 GH MK 13 MK WI BL MY AN
    4 MU RR JW ER MK CP MK RT
    5 MG ES 13 TF JY KR MY MA
    6 MK RR KP ER KS CP 3 MK
    7 MG ES 13 TF JY MK MY MA
    8 MU RR MK ER KS CP 3 RT
    9 MG ES 12 MK JY KR MK MA
    10 JK BK KP WR AK EV 3 KN



    This table has 46 other columns like COLUMN1, COLUMN2, COLUMN3 and so on...

    Now, I need to replace 'MK' with 'SP' in all columns whereever it exists.

    Please let me know, how can I achieve this in efficient way.

    I have 2 solutions but those are not efficient.

    1. I can write multiple UPDATE statements, one for each column.
    UPDATE REG_SCHEDULE SET COLUMN1='SP' WHERE COLUMN1='MK';
    UPDATE REG_SCHEDULE SET COLUMN2='SP' WHERE COLUMN2='MK';
    UPDATE REG_SCHEDULE SET COLUMN3='SP' WHERE COLUMN3='MK';
    UPDATE REG_SCHEDULE SET COLUMN4='SP' WHERE COLUMN4='MK';
    . ..... . . . . . .. and so on for my 46 columns

    2. I can write a function which calls multiple UPDATE statements, one for each column.


    create or replace
    FUNCTION REPLACE_PLACE_HOLDERS( old_place_holder IN VARCHAR2, new_place_holder IN VARCHAR2
    ) RETURN NUMBER
    IS
    updated_rows INTEGER := 0;
    total_updated_rows INTEGER := 0;
    COL VARCHAR2(10);

    BEGIN

    FOR c IN ( SELECT column_name FROM all_tab_columns WHERE table_name = 'LKUP_REGULAR_SCHEDULE')
    LOOP
    dbms_output.put_line ('old_place_holder: ' || old_place_holder );
    if c.column_name != 'SCHEDULE_INDEX' then
    SELECT COUNT(*) INTO updated_rows FROM LKUP_REGULAR_SCHEDULE WHERE c.column_name = old_place_holder;
    UPDATE REG_SCHEDULE SET c.column_name=new_place_holder WHERE c.column_name=old_place_holder;
    dbms_output.put_line ('column_nam: ' || c.column_name || ' changed - ' || updated_rows);
    total_updated_rows := total_updated_rows + updated_rows;
    end if;
    END LOOP;

    dbms_output.put_line('result: ' || total_updated_rows);
    RETURN total_updated_rows;

    END REPLACE_PLACE_HOLDERS;


    The above function is not working somehow….. Its not updating anything.

    But both above solutions are costly as respective to database calls.

    Please let me know, if you have any efficient solution for above problem.

    Thank you very much.
     

    Attached Files:

    • DB.doc
      File size:
      39 KB
      Views:
      2
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot write your function the way you've written it; it can't update anything as it's written now. You need to generate a dynamic update statement and use EXECUTE IMMEDIATE to get this to work:

    Code (SQL):
     
    CREATE OR REPLACE
    FUNCTION REPLACE_PLACE_HOLDERS( old_place_holder IN VARCHAR2, new_place_holder IN VARCHAR2
    ) RETURN NUMBER
    IS
    updated_rows INTEGER := 0;
    total_updated_rows INTEGER := 0;
    COL VARCHAR2(10);
    sqlstmt varchar2(4000);
    BEGIN
    FOR c IN ( SELECT column_name FROM all_tab_columns WHERE TABLE_NAME = 'LKUP_REGULAR_SCHEDULE')
    LOOP
    dbms_output.put_line ('old_place_holder: ' || old_place_holder );
    IF c.column_name != 'SCHEDULE_INDEX' THEN
    sqlstmt:='SELECT COUNT(*) FROM LKUP_REGULAR_SCHEDULE WHERE '||c.column_name||' = '''||old_place_holder||'''';
    EXECUTE immediate sqlstmt RETURN INTO updated_rows;
    sqlstmt:='UPDATE REG_SCHEDULE SET '||c.column_name||'='''||new_place_holder||''' WHERE '||c.column_name||'='''||old_place_holder||'''';
    EXECUTE immediate sqlstmt;
    dbms_output.put_line ('column_nam: ' || c.column_name || ' changed - ' || updated_rows);
    total_updated_rows := total_updated_rows + updated_rows;
    END IF;
    END LOOP;
    commit;
    dbms_output.put_line('result: ' || total_updated_rows);
    RETURN total_updated_rows;
    END REPLACE_PLACE_HOLDERS;
    /
     
    This is, unfortunately, a poor table design, and as such requires inefficient code to maintain the data. I can see no other ways to update such a table and do it efficiently since each column appears to have independent values.