Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Stuck with a simple update problem
Sorry, in stead of products table I meant new_products table, so:
select part_number, line_number, count(*)
from new_products
group by part_number, line_number
having count(*)>1;
will show you which combinations occur more than once.
Good luck (again)!
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 Thu Jun 26 1997 - 00:00:00 CDT