Results 1 to 6 of 6

Thread: Update problem

  1. #1
    Join Date
    Apr 2006
    Posts
    4

    Question Unanswered: Update problem

    Hello!

    The following update is running fine on MS SQL.
    Any suggestions on how to do the same on Oracle?

    update table1
    set table1.col2 = table2.col2
    from table2
    where table1.col1 = table2.col1


  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    A subselect will do it :

    Code:
    update table1
    set table1.col2 = 
       (select table2.col2 
       from table2
       where table1.col1 = table2.col1);
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Apr 2006
    Posts
    4
    Quote Originally Posted by RBARAER
    A subselect will do it :

    Code:
    update table1
    set table1.col2 = 
       (select table2.col2 
       from table2
       where table1.col1 = table2.col1);
    HTH & Regards,

    rbaraer
    This will update all the rows in table1, am I right?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, so you need to add a WHERE clause:
    Code:
    update table1
    set table1.col2 = 
       (select table2.col2 
       from table2
       where table1.col1 = table2.col1)
    where exists
       (select null
       from table2
       where table1.col1 = table2.col1);
    This may also work - it depends on whether table1 is "key-preserved" in the query:
    Code:
    update
    ( select table1.col1 t1col1, table1.col2 t1col2,
             table2.col1 t2col1, table2.col2 t2col2
      from table1, table2
      where table1.col1 = table2.col1
    )
    set t1col2 = t2col2;

  5. #5
    Join Date
    Apr 2006
    Posts
    4
    I have tried using the exists clause but table1 can have too many rows, so I think it will be too slow!!!!


    Thanks

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I have tried using the exists clause but table1 can have too many rows, so I think it will be too slow!!!!
    Only one way to find out...

    An alternative to EXISTS is IN:
    Code:
    update table1
    set table1.col2 = 
       (select table2.col2 
       from table2
       where table1.col1 = table2.col1)
    where table1.col1 in
       (select col1 from table2);

Posting Permissions

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