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>


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)
  7 /
(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)

The only difference I had before was there was only one pair of fields that need to be equal, here I need two pairs as stated above.

Any help or pointers would be much appreciated.

Selina Glynn

-- 
	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.ca
Received on Wed Jun 15 1994 - 21:53:26 CEST

Original text of this message