swapping a two rows in one update statement?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: swapping a two rows in one update statement?

  1. #1
    Join Date
    Jun 2008
    Posts
    2

    swapping a two rows in one update statement?

    how to update two rows of one particular column in one update statement
    for example emp_id 102 name is sam and emp_id 103 name is raj how to interchange or update or swap empid 102 name as raj empid 103 as sam?
    pls reply me but dont answer its possible in plsql like that?
    in one update statement how to swap?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    its possible in plsql

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    I don't see how you could do this in one sql statement, because as soon as you update the first row you've lost the value you need for the second update. Hence why you need plsql

  4. #4
    Join Date
    Jun 2008
    Posts
    2
    without commiting how can i lost first row value also there is not possible of row locking,
    i thing its possible in sql statement, without using plsql?

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    Quote Originally Posted by grajeshkumar82
    i thing its possible in sql statement, without using plsql?
    Tell us how then !

  6. #6
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    Quote Originally Posted by grajeshkumar82
    i thing its possible in sql statement, without using plsql?
    Possible, then do it

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Code:
    SQL> 
    SQL> create  table boring_test
      2  (emp_id number,
      3   name   varchar2(20))
      4  ;
    
    Table created.
    
    SQL> insert into boring_test values(102, 'sam');
    
    1 row created.
    
    SQL> insert into boring_test values(103, 'raj');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select  *
      2  from    boring_test;
    
        EMP_ID NAME
    ---------- --------------------
           102 sam
           103 raj
    
    SQL> 
    SQL> update  boring_test
      2  set     name = decode(name, 'sam','raj','raj','sam',name)
      3  ;
    
    2 rows updated.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select  *
      2  from    boring_test;
    
        EMP_ID NAME
    ---------- --------------------
           102 raj
           103 sam
    
    SQL>
    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.

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