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 update columns in different tables

Discussion in 'SQL PL/SQL' started by Syed Akbar Ali, Mar 5, 2009.

  1. I need some Apps Technical concepts how we will implement screen shots and also HRMS set up with screen shots in real time.

    One important question is that with me right now is to update column in real database Oracle:-

    I have two tables as follows and the update columns are in different tables:

    Table ONE:-

    1 PPS_EMPLOYEE_VU
    PPS_empcode,
    PPS_Insurance_no
    PPS_date_Join
    ,
    ,

    2 PPS_EMP_DOCUMENTS_VU
    PPS_EMPCODE,
    PPS_ENAME,
    PPS_PASSPORT_NO

    Now I have to update the PPS_Insurance_no with pps_passport_no as you see the columns are appearing tables. With normal update statement we cannot update table because the columns are in different tables can you please suggest how I have to proceed and what update statement I have to write .

    One more thing is that data I received in Excel sheet with columns names as Insurance col A , Passport_no B, how I can export in oracle dbase or we have any tool to migrate it.
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Have you considered reading the Implementation and user manuals?
    Please read this excellent article by Rajavu: Update Statements in SQL
    You will have to write a function or procedure to update the table_1 PPS_Insurance_no with PPS_PASSPORT_NO with a cursor. Try the following procedure with your actual table name (code not tested)
    Code (Text):

    CREATE OR REPLACE PROCEDURE update_pps_insurance_no
    AS
       CURSOR c1
       IS
          SELECT t2.pps_passport_no ppn
            FROM table_2 t2, table_1 t1
           WHERE t2.pps_empcode = t1.pps_empcode;
    BEGIN
       FOR c1rec IN c1
       LOOP
          UPDATE table_1
             SET pps_insurance_no = c1rec.ppn
           WHERE pps_empcode = c1rec.pps_empcode;
       END LOOP;
    END pps_insurance_no;
     
    You can use TOAD, better idea is you can write the insert statements in Excel by concatenating the columns in your excel sheet.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Even No need to have a cursor.
    This can be done by using straight SQL .

    I think you need to update the PPS_EMPLOYEE_VU.PPS_Insurance_no with PPS_EMP_DOCUMENTS_VU.pps_passport_no

    Code (Text):

    UPDATE PPS_EMPLOYEE_VU VU1
    SET PPS_INSURANCE_NO = ( SELECT PPS_PASSPORT_NO
                               FROM PPS_EMP_DOCUMENTS_VU VU2
                              WHERE VU2.PPS_EMPCODE = VU1.PPS_EMPCODE)
    WHERE EXISTS ( SELECT NULL
                     FROM PPS_EMP_DOCUMENTS_VU VU2
                    WHERE VU2.PPS_EMPCODE = VU1.PPS_EMPCODE)
     
    This will work if EMPCODE is unique in PPS_EMP_DOCUMENTS_VU.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Btw, there is another method in Toad to load the EXCEL file directly .

    This Option comes in menu
    Database > Import > Import Table Data

    ( As per Toad 9.0.0.10 . Its available in Old version also )

    Here you have to go though a wizard where you specify type of file , file location , starting row number , EXCEL to database table field mapping etc.


    Another method (Oracle's method) is to convert the EXCEL to csv file ( Comma Separated Value ) and use SQL*LOADER or External Table.