DB2 - L

Expand all | Collapse all

Update table query

  • 1.  Update table query

    Posted 4 days ago
    Hello-

    We have a table csdata.polelig where have 2 columns like pol1_desc and pol2_desc.  The table has 500k+ rows we would need to update both columns pol1_desc and pol2_desc to SPACES if it have a PIPE symbol . Could some one let me know how we can do the same ?

    Sample data for policy # 1 in pol1_desc -> test|123-> Should be 'test 123' 

    Regards
    Ron T

    ------------------------------
    RonThomasXYZ Corp
    ------------------------------


  • 2.  RE: Update table query

    Posted 4 days ago
    Hi Ron,

    It can be done with REPLACE function.

    Example: 
    CREATE TABLE DBAGROUP.TB_TEST
    (SID INTEGER
    ,SVAL VARCHAR(3)
    ) ;

    INSERT INTO DBAGROUP.TB_TEST VALUES (1 ,'|') ;
    INSERT INTO DBAGROUP.TB_TEST VALUES (2 ,'||') ;
    INSERT INTO DBAGROUP.TB_TEST VALUES (3 ,'|||') ;
    INSERT INTO DBAGROUP.TB_TEST VALUES (4 ,'A|B') ;
    SELECT * FROM DBAGROUP.TB_TEST;

    UPDATE DBAGROUP.TB_TEST
    SET
    SVAL = REPLACE(SVAL ,'|',' ')
    ;

    SELECT * FROM DBAGROUP.TB_TEST ;
    SELECT SID, SVAL, LENGTH(SVAL) FROM DBAGROUP.TB_TEST;


    Regards

    ------------------------------
    Vamsi Gonella
    IBM India
    ------------------------------



  • 3.  RE: Update table query

    Posted 4 days ago
    Thanks a lot vamsi !

    ------------------------------
    RonThomasXYZ Corp
    ------------------------------



  • 4.  RE: Update table query

    Posted 20 hours ago
    I have an empty table ,I want to force it for index usage. But now it is going with Tablespace scan,Is there any trick!

    Already indices are created those I believe are good choices for index only access path , but due to empty table not being used.

    Thanks for your help 
    Regards 
    Leila 





  • 5.  RE: Update table query

    Posted 19 hours ago

    Try "OPTOMIZE FOR 1 ROW" in your query.  I give it a 50/50 that it will fix your issue.



    ------------------------------
    MarkDoyleUSAA
    ------------------------------



  • 6.  RE: Update table query

    Posted 19 hours ago

    Hi Leila,

     

    ALTER the table to be VOLATILE should force it to use an index.

     

    Regards,

    Paul Ogborne