DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: re-arrange table data

  1. #1
    Join Date
    Jan 2007
    Posts
    231

    re-arrange table data

    hi,

    I have a table food_list, where f_nr is a primary key.

    f_nr food_name
    1 food1
    2 food2
    3 food3
    ....

    up to 50 records i have. I have deleted 10 records randomly. Now i want to re-arrange/re-generate f_nr from 1 to 40.

    Thanks in Advance.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    This can't be a business requirement.
    Is it a school project?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2007
    Posts
    231
    Yeah, this is not the bussiness requirement..
    Is there any way to do that..?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    You are gonna find several ways... here is one.

    1- Drop PK
    2- Rename food_list as old_food_list
    3- Create sequence seq_food_list (a)
    4- Create table food_list as select ... from old_food_list order by f_nr (b)
    5- Build PK
    6- Drop sequence

    (a) here is where you investigate Sequences
    (b) here is where you investigate nexval()

    This process has the beauty of having your original data as a fallback resource in case anything bad happens.
    Last edited by PAVB; 12-18-2007 at 09:26 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Here is a simple demo:

    Code:
    SQL> 
    SQL> drop table t2 purge
      2  /
    
    Table dropped.
    
    SQL> 
    SQL> create table t2 (id int primary key, name varchar2(30))
      2  /
    
    Table created.
    
    SQL> 
    SQL> insert into t2
      2  select rownum, 'AAAA'||to_char(rownum)
      3  from all_source where rownum <= 20
      4  /
    
    20 rows created.
    
    SQL> 
    SQL> select * from t2
      2  /
    
            ID NAME                                                                 
    ---------- ------------------------------                                       
             1 AAAA1                                                                
             2 AAAA2                                                                
             3 AAAA3                                                                
             4 AAAA4                                                                
             5 AAAA5                                                                
             6 AAAA6                                                                
             7 AAAA7                                                                
             8 AAAA8                                                                
             9 AAAA9                                                                
            10 AAAA10                                                               
            11 AAAA11                                                               
    
            ID NAME                                                                 
    ---------- ------------------------------                                       
            12 AAAA12                                                               
            13 AAAA13                                                               
            14 AAAA14                                                               
            15 AAAA15                                                               
            16 AAAA16                                                               
            17 AAAA17                                                               
            18 AAAA18                                                               
            19 AAAA19                                                               
            20 AAAA20                                                               
    
    20 rows selected.
    
    SQL> 
    SQL> delete t2 where id in (4, 8, 13, 17,19)
      2  /
    
    5 rows deleted.
    
    SQL> 
    SQL> commit
      2  /
    
    Commit complete.
    
    SQL> 
    SQL> select * from t2
      2  /
    
            ID NAME                                                                 
    ---------- ------------------------------                                       
             1 AAAA1                                                                
             2 AAAA2                                                                
             3 AAAA3                                                                
             5 AAAA5                                                                
             6 AAAA6                                                                
             7 AAAA7                                                                
             9 AAAA9                                                                
            10 AAAA10                                                               
            11 AAAA11                                                               
            12 AAAA12                                                               
            14 AAAA14                                                               
    
            ID NAME                                                                 
    ---------- ------------------------------                                       
            15 AAAA15                                                               
            16 AAAA16                                                               
            18 AAAA18                                                               
            20 AAAA20                                                               
    
    15 rows selected.
    
    SQL> 
    SQL> 
    SQL> 
    SQL> update t2 a set id = (select rn
      2  			    from (select rownum rn, name from t2 order by id, name) b
      3  			   where b.name = a.name )
      4  where a.name in ( select name
      5  		       from   t2 c)
      6  /
    
    15 rows updated.
    
    SQL> 
    SQL> 
    SQL> select * from t2
      2  /
    
            ID NAME                                                                 
    ---------- ------------------------------                                       
             1 AAAA1                                                                
             2 AAAA2                                                                
             3 AAAA3                                                                
             4 AAAA5                                                                
             5 AAAA6                                                                
             6 AAAA7                                                                
             7 AAAA9                                                                
             8 AAAA10                                                               
             9 AAAA11                                                               
            10 AAAA12                                                               
            11 AAAA14                                                               
    
            ID NAME                                                                 
    ---------- ------------------------------                                       
            12 AAAA15                                                               
            13 AAAA16                                                               
            14 AAAA18                                                               
            15 AAAA20                                                               
    
    15 rows selected.
    
    SQL> 
    SQL> exit

  6. #6
    Join Date
    Jan 2007
    Posts
    231
    Thanks Tamil, really outstanding..,
    I spent lot of time on this.,

    Thanks Pavb,

  7. #7
    Join Date
    Dec 2007
    Location
    MUMBAI, India
    Posts
    2
    Thats excellent tamil...
    really amazing stuff:-)

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Basically I do not like to drop objects or recreate objects for my simple work.

    Always try to accomplish the task in a single SQL statement. If SQL is not possible, then opt for PL/SQL. If PL/SQL is not possible, then choose JAVA.

  9. #9
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    tamilselvan...
    This guy rocks man. Whenever you ask any developement related questions he gets tons of ways to explain it.
    http://www.perf-engg.com
    A performance engineering forum

  10. #10
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    great stuff...shows how much I still got to go...
    Looking for the greatest evil in the world? Look in the mirror.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width