Re: Stuck with a simple update problem

Atif Ahmad Khan wrote:
>
> I have been updating tables from other tables using the
> following :
>
> (snip)
> 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
>
> (snip)

Use IN (subquery), not "="

It is unfortunate that the same error message is given: a) where the subquery returns more than one row (legitimate) and b) where the comparison is between sets of two or more items

(I am assuming that (part_number, line_number) IS unique for new_products! If not, you will still get the error message, but correctly this time)

Hope this helps.

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"