multiple values updates
From: Selina Glynn <selina_at_dciem.dnd.ca>
Date: 15 Jun 94 19:53:26 GMT
Message-ID: <29172_at_dciem.dciem.dnd.ca>
Date: 15 Jun 94 19:53:26 GMT
Message-ID: <29172_at_dciem.dciem.dnd.ca>
I need to update a field from the values of another table. I have written
a similar update statement before, it used to work now its complains:
SQL> update divername
2 set file_no =
3 (select b.fn
4 from divername a, pers_info b
5 where a.last_name = b.lastn
6 and a.first_name = b.firstn)
(select b.fn
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
The table structure of a is:
SQL> desc divername
Name Null? Type
------------------------------- -------- ----
DIVER_NUM NOT NULL NUMBER(6) SOURCE NOT NULL CHAR(1) LAST_NAME NOT NULL CHAR(20) AFFILIATION CHAR(20) BIRTHDATE DATE MILITARY CHAR(1) SEX CHAR(1) REMARK CHAR(80) HEIGHT NUMBER FIRST_NAME CHAR(20) FILE_NO CHAR(6)
The table structure of a is:
SQL> desc pers_info
Name Null? Type
------------------------------- -------- ----
FN CHAR(5) LASTN CHAR(20) FIRSTN CHAR(20) AGE CHAR(2) SEX CHAR(1) MIL CHAR(1) AFFIL CHAR(20) RMKS CHAR(80)
-- Selina Glynn, DCIEM, Canadian Forces Base Toronto UUCP: {decvax|ihnp4|watmath}!utzoo!dciem!dretor!selina or uunet!mnetor!dciem!dretor!selina or nrcaer!dciem!dretor!selina Internet: selina_at_dretor.dciem.dnd.caReceived on Wed Jun 15 1994 - 21:53:26 CEST