Re: Stuck with a simple update problem

Hi Atif,

It seems like the combination of part_number and line_number is not unique in your products table.

Try:
select part_number, line_number, count(*) from products
group by part_number, line_number
having count(*)>1;

This will show you which combinations occur more than once.

Good luck!

Jack.
aak2_at_Ra.MsState.Edu (Atif Ahmad Khan) wrote:

>
>I have been updating tables from other tables using the
>following :
>
>update products
>set ( field1, field2 )
> = ( select field1, field2 from new_products
> where new_products.part_number = products.part_number )
>where exists ( select NULL from new_products
> where new_products.part_number = products.part_number )
>
>
>However now I have a table that doesn't have a single column key
>field. But is keyed on 2 columns part_number, line_number. I
>tried using the following :
>
>update products
>set ( field1, field2 )
> = ( select field1, field2 from new_products
> where new_products.part_number = products.part_number
> and new_products.line_number = products.line_number )
>where exists ( select NULL from new_products
> where new_products.part_number = products.part_number
> and new_products.line_number = products.line_number )
>
>
>But I keep getting the following error :
>
>ERROR at line 7:
>ORA-01427: single-row subquery returns more than one row
>
>I have tried using aliases but that didn't help. Maybe the answer
>is too simple and obvious. But it sure has been alluding me and has
>kept me up almost all night now.
>
>I would apprecoiate any helpful hints.
>
>Thanks.
>
>Atif Khan
>aak2_at_ra.msstate.edu
Received on Wed Jun 25 1997 - 00:00:00 CDT