Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Stuck with a simple update problem

Re: Stuck with a simple update problem

From: Jack Ploeg <jploeg_at_pi.net>
Date: 1997/06/26
Message-ID: <33b2dbc4.352652@news.pi.net>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US