SQL - Updating Tables

[prouse]

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)

[BRAUK]

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

[prouse]

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

[akkerend]

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.