Update a table with values from same table AND another table

in Oracle Database; Hello all this should be simple... maybe i'm getting old. T1 has fields ID,NAME,OLDNAME T2 has fields ID,NAME ID is ...
+ Post Reply + Post New Topic
Results 1 to 3 of 3
  1. #1
    makidrin is offline Junior Member
    Points: 40, Level: 1
    Join Date
    07 Dec 2009
    Posts
    1
    Points
    40

    Question Update a table with values from same table AND another table

    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. #2
    Sikkandar.S.P is offline Junior Member
    Points: 180, Level: 3
    Join Date
    16 Jan 2009
    Location
    Chennai
    Posts
    16
    Points
    180

    Re: Update a table with values from same table AND another table

    I will just show you an example with a Single data,

    Code :
    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
    Thanks,
    Sikki

  3. #3
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Update a table with values from same table AND another table

    Nice explanation Sikki.

    There is another way also for doing it.

    UPDATE T1
    SET T1.OLDNAME=T1.NAME,T1.NAME=T2.NAME
    FROM T1 INNER JOIN T2 ON T1.ID=T2.ID;
    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 )

    Raj.

Other Solutions
  1. Replies: 1
    Last Post: 10-27-2010, 04:15 PM
  2. How to get all the column values from a table using table source
    By diwakar.shenoy@gmail.com in forum Oracle Apps Technical
    Replies: 0
    Last Post: 02-01-2010, 03:58 AM
  3. Replies: 8
    Last Post: 12-15-2009, 10:17 AM
  4. Replies: 1
    Last Post: 11-10-2009, 02:31 PM