Re: multiple values updates

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
>
You subquery is specific enough, or you've simply got som redundant data in the divername, pers_info table(s). One way to circumvent the error is to select max(b.fn) in the subquery, but that's just ignoring the problem.

To ferret out the offending row(s), issue:

	select b.fn, count(*)
	from divername a, pers_info b
	where a.last_name = b.lastn
	and a.first_name = b.firstn
	group by b.fn
	having count(*) > 1