This discussion is archived
7 Replies Latest reply: Feb 1, 2013 10:41 PM by shiva_0004 RSS

How to update table with running numbers

shiva_0004 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you all for your response.

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points