1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. 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!

Update a table with values from same table AND another table

Discussion in 'SQL PL/SQL' started by makidrin, Dec 7, 2009.

  1. makidrin

    makidrin Guest

    Hello all

    this should be simple... maybe i'm getting old.

    T1 has fields ID,NAME,OLDNAME T2 has fields ID,NAME

    ID is primary key in both tables.

    all IDs in T2 are already present in T1

    I need a query that updates T1.OLDNAME to the value of T1.NAME AND T1.NAME to T2.NAME for corresponding IDs.

    This is how I do it in ms sql server, (I know this sintax is not valid in oracle, its just to make it clear):

    UPDATE T1
    SET T1.OLDNAME=T1.NAME,T1.NAME=T2.NAME
    FROM T1 INNER JOIN T2 ON T1.ID=T2.ID;

    T1 contains about 130,000 records, T2 contains about 27,000.

    DB is oracle 10g
    Any help will be greatly appreciated.
     
  2. Sikkandar.S.P

    Sikkandar.S.P Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Chennai
    I will just show you an example with a Single data,

    Code (Text):
    SQL >CREATE TABLE T1 (ID NUMBER PRIMARY KEY,NAME VARCHAR2(10), OLDNAME VARCHAR2(10))
      2  /

    Table created.

    SQL >CREATE TABLE T2 (ID NUMBER PRIMARY KEY,NAME VARCHAR2(10))
      2  /

    Table created.

    SQL >INSERT INTO T1 VALUES (1,'name1','oldname1')
      2  /

    1 row created.

    SQL >INSERT INTO T2 VALUES (1,'name2')
      2  /

    1 row created.

    SQL >COMMIT
      2  /

    Commit complete.

    SQL >SELECT * FROM T1
      2  /

            ID NAME       OLDNAME
    ---------- ---------- ----------
             1 name1      oldname1

    SQL >SELECT * FROM T2
      2  /

            ID NAME
    ---------- ----------
             1 name2

    SQL >UPDATE (SELECT A.ID,
      2                 A.OLDNAME,
      3                 A.NAME T1_NAME,
      4                 B.NAME T2_NAME
      5          FROM   T1 A,
      6                 T2 B
      7          WHERE  A.ID = B.ID)
      8  SET    OLDNAME = T1_NAME,
      9         T1_NAME = T2_NAME
     10  /

    1 row updated.

    SQL >COMMIT
      2  /

    Commit complete.

    SQL >SELECT * FROM T1
      2  /

            ID NAME       OLDNAME
    ---------- ---------- ----------
             1 name2      name1
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Nice explanation Sikki.

    There is another way also for doing it.

    Syntax is slightly different in Oracle. It should be as follows.

    Code (SQL):

    UPDATE T1 A
       SET A.OLDNAME = A.NAME,
           A.NAME    = ( SELECT B.NAME
                           FROM T2 B
                          WHERE A.ID  =  B.ID )
    WHERE EXISTS ( SELECT NULL
                           FROM T2 B
                          WHERE A.ID  =  B.ID )