Forum Stats

  • 3,780,546 Users
  • 2,254,409 Discussions
  • 7,879,377 Comments

Discussions

Updating a column to NULL in a table having 62m records

curious_mind
curious_mind Member Posts: 254 Bronze Badge

Hi All,

i have a requirement where there is a need to update a column to NULL, depending upon a column existing into one another table and that query to check the existence is below:

select count(*) from DMU_CT18_ACCT_DSTR where acct_alt_id in (select acct_alt_id from seods01.dstr_alt_id where length(acct_alt_id)=27);--62765794

There is a column called EODS_DSTR_ID which needs to be updated in the DMU_CT18_ACCT_DSTR table, please suggest the best way to do this updation on more than 62 millions records.

Tagged:

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,005 Red Diamond

    It isn't clear. I'll assume you want to update table DMU_CT18_ACCT_DSTR column EODS_DSTR_ID to NULL if column ACCT_ALT_ID length is 27 and there is no matching ACCT_ALT_ID in table SEODS01.DSTR_ALT_ID:

    UPDATE DMU_CT18_ACCT_DSTR T
      SET T.EODS_DSTR_ID = NULL
     WHERE LENGTH(T.ACCT_ALT_ID) = 27
       AND NOT EXISTS (
                       SELECT NULL
                         FROM SEODS01.DSTR_ALT_ID S
                         WHERE S.ACCT_ALT_ID = T.ACCT_ALT_ID
                      )
    /
    

    SY.

    curious_mind
  • curious_mind
    curious_mind Member Posts: 254 Bronze Badge

    sorry for not making it so much clear.

    i have to update table DMU_CT18_ACCT_DSTR column EODS_DSTR_ID to NULL for all those acct_alt_id column which are existing in other table dstr_alt_id  and having length of 27.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,371 Red Diamond
    edited Jan 21, 2021 2:57PM

    Hi,

    Here's one way:

    UPDATE dmu_ct18_acct_dstr 
    SET    eods_dstr_id = NULL
    WHERE  acct_alt_id IN (
                              SELECT acct_alt_id
               	          FROM  dstr_alt_id
    		      )
    AND   LENGTH (acct_alt_id) = 27
    AND   eods_dstr_id         IS NOT NULL
    ;
    

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    See: How to Ask Questions in Developer Community Spaces - oracle-tech

  • curious_mind
    curious_mind Member Posts: 254 Bronze Badge
    UPDATE dmu_ct18_acct_dstr 
    SET    eods_dstr_id = NULL
    WHERE  acct_alt_id IN (
                              SELECT acct_alt_id
               	          FROM  dstr_alt_id
    		      )
    AND   LENGTH (acct_alt_id) = 27
    AND   eods_dstr_id         IS NOT NULL
    ;
    

    looks fine but for 62 million records it doesn't look like, please suggest.


    Oracle version which we are using is 12c.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,005 Red Diamond

    If number of to be updated rows is relatively small comparing to total number of rows in table DMU_CT18_ACCT_DSTR you could create function-based index and use:

    CREATE INDEX DMU_CT18_ACCT_DSTR_IDX1
      ON DMU_CT18_ACCT_DSTR(
                            CASE
                              WHEN     EODS_DSTR_ID IS NOT NULL
                                   AND
                                       LENGTH(ACCT_ALT_ID) = 27
                                TNEN ACCT_ALT_ID
                            END
                           )
    /
    UPDATE DMU_CT18_ACCT_DSTR
      SET EODS_DSTR_ID = NULL
     WHERE CASE
             WHEN     EODS_DSTR_ID IS NOT NULL
                  AND
                      LENGTH(ACCT_ALT_ID) = 27
               TNEN ACCT_ALT_ID
           END IN (
                   SELECT ACCT_ALT_ID
                    FROM SEODS01.DSTR_ALT_ID
                  )
    /
    

    SY.