1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to interchange values of two columns in a table?!

Discussion in 'SQL PL/SQL' started by Vicky, Jul 11, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How to interchange values of two columns in a table with some criteria?!

    the query below works,

    update employees
    set emp_id = salary,
    salary = emp_id
    where salary=5000

    But will it create any impact in any place.,?!
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    For sure, it will definitely have an impact ... technically and from the business perspective point of view and you should avoid doing such updates in real-life situations !

    Take the employees table for example, you may end up violating the constraints by updating emp_id - which is generally the primary key for the table and most probably referenced by another table in a foreign - assuming a properly designed database :) .

    And just imagine the consequences if you update salary from say $1000 to emp_id= 123456789 :p
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    :)S Rajen.,

    I just wrote tat to show as a exmpl for intrchanging,

    i) So, its is not necessary to go for any temporary table and all before interchanging two similar columns, right.,?!

    ii) The simple update query which I used above is enough for interchanging two similar columns, right.,?!
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    This is the power of Oracle (and most of today's RDBMS I suppose) ...
    i) Yes it's not necessary as long as the data types of the 2 columns are same (or compatible)
    ii) Yes.

    The SQL engine SELECTs the employees according to you WHERE conditions, and then does the UPDATEs: it updates EMP_ID with the content of SALARY (initial value of SALARY) and updates SALARY with initial value of EMP_ID, just like in a database trigger, where Oracle knows what the :OLD.column_value and :NEW.column_value are...
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, presuming that one of those columns is not a primary key column or has a unique index created on it:


    Code (SQL):

    SQL> UPDATE employees
      2   SET employee_id = salary,
      3   salary = employee_id
      4   WHERE salary=9000
      5  /
    UPDATE employees
    *
    ERROR at line 1:
    ORA-00001: UNIQUE CONSTRAINT (GRIBNAUT.EMP_EMP_ID_PK) violated

    SQL>
     

    You must really take great care before attempting such an action.