7 Replies Latest reply: Feb 2, 2013 12:41 AM by shiva_0004 RSS

    How to update table with running numbers

    shiva_0004
      Hi,

      I have requirement like, there is a table loc_tab created as below,

      CREATE TABLE loc_tab
      (
      country_id NUMBER,
      country_code VARCHAR2(3),
      country_name VARCHAR2(50),
      state_id NUMBER,
      state_code VARCHAR2(3),
      state_name VARCHAR2(50),
      city_id NUMBER,
      city_code VARCHAR2(3),
      city_name VARCHAR2(50)
      );

      I have inserted records as below,
      COUNTRY_ID     COUNTRY_CODE     COUNTRY_NAME     STATE_ID     STATE_CODE     STATE_NAME     CITY_ID     CITY_CODE     CITY_NAME
      
                IND          INDIA                    TN          TAMIL NADU          CHN          CHENNAI
                IND          INDIA                    TN          TAMIL NADU          TRI          TRICHY
                IND          INDIA                    TN          TAMIL NADU          CMT          COIMBATORE
                IND          INDIA                    TN          TAMIL NADU          MDU          MADURAI
                IND          INDIA                    AP          ANDHRA PRADESH          HYD          HYDERABAD
                IND          INDIA                    AP          ANDHRA PRADESH          SEC          SECUNDRABAD
                AUS          AUSTRALIA               QLD          QUEENSLAND          BRI          BRISBANE
                AUS          AUSTRALIA               TAS          TASMANIA          HB          HOBART
                AUS          AUSTRALIA               TAS          TASMANIA          CCE          CITY OF CLEARANCE
                AUS          AUSTRALIA               TAS          TASMANIA          BUR          BURNIE
      Now, I wanted to update the table such that all the ID columns are updated with running number.

      Each ID columns should get incremented in such a way that, for Country_ID column, for "India" if country_id is 1, than it should be one for all the rows having name as "India". Same way for "Australia".

      In case of State it also holds the same logic with repetitive numbers till same state name comes.

      For City, it of course will hold distinct IDs only because City name will not get duplicated.

      This update needs to be done in a normal way using simple SQL such that no PLSQL like looping, etc.. is involved.

      Below is the table content, and this is how the table needs to be updated,
      COUNTRY_ID     COUNTRY_CODE     COUNTRY_NAME     STATE_ID     STATE_CODE     STATE_NAME     CITY_ID     CITY_CODE     CITY_NAME
                                              
      1          IND          INDIA          1          TN          TAMIL NADU     1     CHN          CHENNAI
      1          IND          INDIA          1          TN          TAMIL NADU     2     TRI          TRICHY
      1          IND          INDIA          1          TN          TAMIL NADU     3     CMT          COIMBATORE
      1          IND          INDIA          1          TN          TAMIL NADU     4     MDU          MADURAI
      1          IND          INDIA          2          AP          ANDHRA PRADESH     1     HYD          HYDERABAD
      1          IND          INDIA          2          AP          ANDHRA PRADESH     2     SEC          SECUNDRABAD
      2          AUS          AUSTRALIA     1          QLD          QUEENSLAND     1     BRI          BRISBANE
      2          AUS          AUSTRALIA     2          TAS          TASMANIA     1     HB          HOBART
      2          AUS          AUSTRALIA     2          TAS          TASMANIA     2     CCE          CITY OF CLEARANCE
      2          AUS          AUSTRALIA     2          TAS          TASMANIA     3     BUR          BURNIE
      Thanks & Regards,
      Shiva
        • 1. Re: How to update table with running numbers
          rp0428
          That is pretty much what the analytic function ROW_NUMBER does.

          If you search this forum for ROW_NUMBER you will find plenty of posts with examples.

          Here is a simple example using the SCOTT.EMP table to assign sequential numbers to each set of rows with the same department number
          select empno, ename, deptno, row_number() over (partition by deptno order by deptno) rn
          from emp 
          
          EMPNO     ENAME     DEPTNO     RN
          7782     CLARK     10     1
          7839     KING     10     2
          7934     MILLER     10     3
          7566     JONES     20     1
          7788     SCOTT     20     2
          7876     ADAMS     20     3
          7902     FORD     20     4
          7369     SMITH     20     5
          7521     WARD     30     1
          7499     ALLEN     30     2
          7900     JAMES     30     3
          7698     BLAKE     30     4
          7654     MARTIN     30     5
          See the ROW_NUMBER analytic function in the SQL Language doc
          http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions154.htm
          • 2. Re: How to update table with running numbers
            Purvesh K
            Untested
            merge into loc_tab tgt
            using (
                      select country_code, state_code, city_code, 
                               dense_rank() over (order by country_code) rnk_country_id,
                               dense_rank() over (partition by country_code order by state_code) rnk_state_id,
                               row_number() over (partition by country_code, state_code order by state_code) rnk_city_id ---> Change order by to City_Code 
                                                                                                                    --if you wish to order the Cities according to Code, else keep the same to random order
                        from loc_tab
                    ) src
               on ( tgt.country_code = src.country_code
                      and tgt.state_code = src.state_code
                      and tgt.city_code = src.city_code
                   )
            when matched then
               update set city_id = rnk_city_id,
                               country_id = rnk_country_id,
                               state_id = rnk_state_id
            From Data posted by Karthick_arp's post: (Test Results after executing above posted Merge Statement)
            select *
              from loc_tab;
            
            COUNTRY_ID             COUNTRY_CODE COUNTRY_NAME                                       STATE_ID               STATE_CODE STATE_NAME                                         CITY_ID                CITY_CODE CITY_NAME                                          
            ---------------------- ------------ -------------------------------------------------- ---------------------- ---------- -------------------------------------------------- ---------------------- --------- -------------------------------------------------- 
            2                      IND          INDIA                                              2                      TN         TAMIL NADU                                         3                      CHN       CHENNAI                                            
            2                      IND          INDIA                                              2                      TN         TAMIL NADU                                         2                      TRI       TRICHY                                             
            2                      IND          INDIA                                              2                      TN         TAMIL NADU                                         1                      CMT       COIMBATORE                                         
            2                      IND          INDIA                                              2                      TN         TAMIL NADU                                         4                      MDU       MADURAI                                            
            2                      IND          INDIA                                              1                      AP         ANDHRA PRADESH                                     1                      HYD       HYDERABAD                                          
            2                      IND          INDIA                                              1                      AP         ANDHRA PRADESH                                     2                      SEC       SECUNDRABAD                                        
            1                      AUS          AUSTRALIA                                          1                      QLD        QUEENSLAND                                         1                      BRI       BRISBANE                                           
            1                      AUS          AUSTRALIA                                          2                      TAS        TASMANIA                                           2                      HB        HOBART                                             
            1                      AUS          AUSTRALIA                                          2                      TAS        TASMANIA                                           1                      CCE       CITY OF CLEARANCE                                  
            1                      AUS          AUSTRALIA                                          2                      TAS        TASMANIA                                           3                      BUR       BURNIE                                             
            
             10 rows selected
            Edited by: Purvesh K on Feb 1, 2013 1:37 PM
            --Added Test Results                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
            • 3. Re: How to update table with running numbers
              Karthick_Arp
              When posting INPUT DATA post it as insert statement like this
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name) values ('IND', 'INDIA', 'TN', 'TAMIL NADU', 'CHN', 'CHENNAI');
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name)values ('IND', 'INDIA', 'TN', 'TAMIL NADU', 'TRI', 'TRICHY');
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name)values ('IND', 'INDIA', 'TN', 'TAMIL NADU', 'CMT', 'COIMBATORE');
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name)values ('IND', 'INDIA', 'TN', 'TAMIL NADU', 'MDU', 'MADURAI');
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name)values ('IND', 'INDIA', 'AP', 'ANDHRA PRADESH', 'HYD', 'HYDERABAD');
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name)values ('IND', 'INDIA', 'AP', 'ANDHRA PRADESH', 'SEC', 'SECUNDRABAD');
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name)values ('AUS', 'AUSTRALIA', 'QLD', 'QUEENSLAND', 'BRI', 'BRISBANE');
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name)values ('AUS', 'AUSTRALIA', 'TAS', 'TASMANIA  ', 'HB ', 'HOBART');
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name)values ('AUS', 'AUSTRALIA', 'TAS', 'TASMANIA  ', 'CCE', 'CITY OF CLEARANCE');
              insert into loc_tab (country_code, country_name, state_code, state_name, city_code, city_name)values ('AUS', 'AUSTRALIA', 'TAS', 'TASMANIA  ', 'BUR', 'BURNIE');
              You can update the ID columns like this
              SQL> select * from loc_tab
                2  /
               
              COUNTRY_ID COU COUNTRY_NAME           STATE_ID STA STATE_NAME              CITY_ID CIT CITY_NAME
              ---------- --- -------------------- ---------- --- -------------------- ---------- --- --------------------
                         IND INDIA                           TN  TAMIL NADU                      CHN CHENNAI
                         IND INDIA                           TN  TAMIL NADU                      TRI TRICHY
                         IND INDIA                           TN  TAMIL NADU                      CMT COIMBATORE
                         IND INDIA                           TN  TAMIL NADU                      MDU MADURAI
                         IND INDIA                           AP  ANDHRA PRADESH                  HYD HYDERABAD
                         IND INDIA                           AP  ANDHRA PRADESH                  SEC SECUNDRABAD
                         AUS AUSTRALIA                       QLD QUEENSLAND                      BRI BRISBANE
                         AUS AUSTRALIA                       TAS TASMANIA                        HB  HOBART
                         AUS AUSTRALIA                       TAS TASMANIA                        CCE CITY OF CLEARANCE
                         AUS AUSTRALIA                       TAS TASMANIA                        BUR BURNIE
               
              10 rows selected.
              
              
              SQL> merge into loc_tab a
                2  using (
                3          select dense_rank() over(order by country_code) country_id_new,
                4                 country_code,
                5                 country_name,
                6                 dense_rank() over(partition by country_code order by state_code) state_id_new,
                7                 state_code,
                8                 state_name,
                9                 dense_rank() over(partition by country_code, state_code order by city_code) city_id_new,
               10                 city_code,
               11                 city_name
               12            from loc_tab
               13         ) b
               14      on (
               15            a.rowid = b.rowid
               16         )
               17  when matched then
               18      update set country_id = country_id_new,
               19                 state_id   = state_id_new,
               20                 city_id    = city_id_new
               21  /
               
              10 rows merged.
               
              SQL> select * from loc_tab order by country_id, state_id, city_id;
               
              COUNTRY_ID COU COUNTRY_NAME           STATE_ID STA STATE_NAME              CITY_ID CIT CITY_NAME
              ---------- --- -------------------- ---------- --- -------------------- ---------- --- --------------------
                       1 AUS AUSTRALIA                     1 QLD QUEENSLAND                    1 BRI BRISBANE
                       1 AUS AUSTRALIA                     2 TAS TASMANIA                      1 BUR BURNIE
                       1 AUS AUSTRALIA                     2 TAS TASMANIA                      2 CCE CITY OF CLEARANCE
                       1 AUS AUSTRALIA                     2 TAS TASMANIA                      3 HB  HOBART
                       2 IND INDIA                         1 AP  ANDHRA PRADESH                1 HYD HYDERABAD
                       2 IND INDIA                         1 AP  ANDHRA PRADESH                2 SEC SECUNDRABAD
                       2 IND INDIA                         2 TN  TAMIL NADU                    1 CHN CHENNAI
                       2 IND INDIA                         2 TN  TAMIL NADU                    2 CMT COIMBATORE
                       2 IND INDIA                         2 TN  TAMIL NADU                    3 MDU MADURAI
                       2 IND INDIA                         2 TN  TAMIL NADU                    4 TRI TRICHY
               
              10 rows selected.
               
              SQL> 
              Edited by: Karthick_Arp on Jan 31, 2013 11:45 PM
              Modified the MERGE statement to include PARTITION BY
              • 4. Re: How to update table with running numbers
                Purvesh K
                Karthick_Arp wrote:
                SQL> select * from loc_tab order by country_id, state_id, city_id
                2 /

                COUNTRY_ID COU COUNTRY_NAME STATE_ID STA STATE_NAME CITY_ID CIT CITY_NAME
                ---------- --- -------------------- ---------- --- -------------------- ---------- --- --------------------
                1 AUS AUSTRALIA 1 QLD QUEENSLAND 1 BRI BRISBANE
                1 AUS AUSTRALIA 2 TAS TASMANIA 2 BUR BURNIE
                1 AUS AUSTRALIA 2 TAS TASMANIA 3 CCE CITY OF CLEARANCE
                1 AUS AUSTRALIA 2 TAS TASMANIA 4 HB HOBART
                2 IND INDIA 3 AP ANDHRA PRADESH 5 HYD HYDERABAD
                2 IND INDIA 3 AP ANDHRA PRADESH 6 SEC SECUNDRABAD
                2 IND INDIA 4 TN TAMIL NADU 7 CHN CHENNAI
                2 IND INDIA 4 TN TAMIL NADU 8 CMT COIMBATORE
                2 IND INDIA 4 TN TAMIL NADU 9 MDU MADURAI
                2 IND INDIA 4 TN TAMIL NADU 10 TRI TRICHY

                10 rows selected.

                State ID and City ID are to be re-set for every Country and State respectively, isn't it? So a Partition by would be required.
                • 5. Re: How to update table with running numbers
                  Karthick_Arp
                  State ID and City ID are to be re-set for every Country and State respectively, isn't it? So a Partition by would be required.
                  Yes correct, silly me :)
                  • 6. Re: How to update table with running numbers
                    shiva_0004
                    Thank you all for your response.

                    With this I understood so many things can be done with Merge.
                    • 7. Re: How to update table with running numbers
                      shiva_0004
                      Thank you all for your response.

                      With this I understood so many things can be done with Merge.