Re: multiple values updates

From: Phil Huber <phil_at_mtu.edu>
Date: 16 Jun 1994 08:11:24 -0400
Message-ID: <2tpfhc$2bn_at_ctsad5.cts>

: In article <29172_at_dciem.dciem.dnd.ca>,
: Selina Glynn <selina_at_dciem.dnd.ca> wrote:
: >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 problem is that you should not be repeating the divername table in the subquery. It should read:

update divername
set file_no =
(select fn from pers_info
where lastn = last_name
and firstn = first_name)

This is assuming that there aren't 2 rows in pers_info with the same name.

Good Luck, Phil

  +---------------------------------------------------------------------+

  | Phillip Huber Analyst/Programmer Michigan Technological Univ. |   | Internet: phil_at_mtu.edu Phone:(906)487-2223 Fax:(906)487-2521 |

 +---------------------------------------------------------------------+

-- 
  +---------------------------------------------------------------------+
  |  Phillip Huber   Analyst/Programmer   Michigan Technological Univ.  |
  |  Internet: phil_at_mtu.edu    Phone:(906)487-2223    Fax:(906)487-2521 |
  +---------------------------------------------------------------------+

Received on Thu Jun 16 1994 - 14:11:24 CEST