Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    28

    Unanswered: Updating between 2 tables.

    This seems so simple yet I have been struggling with how to do it.

    I have 2 tables. Both have similar Primary Keys. I want to update the data in column 2 of my first table, with the data from Column 3 on my second where the Primary Keys match.

    Sample

    Code:
     
    UPDATE TABLE_1
    SET
    COLUMN_2 = <Here’s the problem, I want the data from Column_3 on table 2 where the PK’s match>
    WHERE
    COLUMN_1 = (SELECT COLUMN_1 FROM TABLE_2 WHERE COLUMN_3 > 100)
    /

    Any idea’s?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    28
    Quote Originally Posted by r937
    which database?
    I am using Oracle 9i

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let's move this thread to the oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Either of..
    Code:
    update table_1
       set column_2 = ( select column_3
                          from table_2
                         where primary_key = table_1.primary_key
                           and column_3 > 100 )
     where exists ( select null
                      from table_2
                     where primary_key = table_1.primary_key
                       and column_3 > 100 )
    /
    or
    Code:
    update ( select table_2.column_3 table_2_column_3, table_1.column_2 table_1_column_2
               from table_1, table_2
              where table_1.primary_key = table_2.primary_key
                and table_2.column_3 > 100 )
       set table_1_column_2 = table_2_column_3
    /
    should work. Allthough, the latter one proves to be more efficient.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •