SQL - Updating Tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL - Updating Tables

  1. #1
    Join Date
    Dec 2000
    Posts
    5
    Is it possible to update the records a column of one table with the values from a column in another table based on a primary key between the two tables?

    For example:
    update the values of column B of table 1 with the values of column B in table 2 where table1.columnA = table2.columnA.

    When I attempt this I get a "single row query returns more than one row' error.

    Thanks

    PMR (DBAWannabe)

  2. #2
    Join Date
    Jun 2000
    Location
    Memphis,TN
    Posts
    30
    i guess u will hv to use cursor like this:
    declare cursor c1 is
    select cola ,colb from table2;
    v_cola t2.cola%type;
    v_colb t2.colb%type;
    begin
    open c1;
    loop
    fetch c1 into v_cola,v_colb;
    exit when c1%notfound;
    update table1
    set colb = v_colb
    where cola = v_cola;
    end loop;
    end c1;
    end;
    /
    once u verify that values are ok issue a commit;

    HTH

  3. #3
    Join Date
    Dec 2000
    Posts
    5

    Thanks

    That worked fine.

    Initially I was wondering if this could be done without PL/SQL, but the sample code worked fine

    I have a book called 'The SQl Guide to ORACLe' by Van Der Lans and it has the follwing sample code which leads me to believe you can do an update of this type in SQLPLUS:

    UPDATE PLAYERS
    SET NUMBER_OF_SETS =
    (SELECT WON + LOST
    FROM MATCHES
    WHERE PLAYERNO = PLAYERS.PLAYERNO)
    WHERE PLAYERNO IN
    (SELECT PLAYERNO
    FROM MATCHES)


    I attempted a similar update, but received an error. This book is about 9 years old so I was wondering if this was possible in older versions of ORACLE but not in V7 or above.

    Thanks

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    You don't need PL/SQL, it can be done in SQL, but only if the subquery returns just one row.
    If
    (SELECT WON + LOST
    FROM MATCHES
    WHERE PLAYERNO = PLAYERS.PLAYERNO)
    returns two rows, Oracle doesn't know which of the two rows has the new value of NUMBER_OF_SETS. Then you get the 'single row query returns more than one row' error.



Posting Permissions

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



Click Here to Expand Forum to Full Width