Re: Weird problem: Update statement updating records it should not

There are quite a few tables involved and views also so all the DDLs would be a lot of information.

The update is below. Please note the two inner selects are identical (bar the fields returned and the last line of the first select which ties the inner records to the records to be updated in "qlimUpdate"). This update should only update records in svc_quote_line_import where matches are found in associated tables and leave all other records alone. This is what it does on 9i.

The error is ORA-01407: cannot update
("NETPRICET"."SVC_QUOTE_LINE_IMPORT"."QUOTE_LINE_IMPORT_ID") to NULL.

Please note QUOTE_LINE_IMPORT_ID is being set to 1 in the update. The only way it would be set to null is if the second inner select returns more records than the first inner select.

When I run both selects on their own both return 49 records (they are identical after all). When the update runs the second select seems to "find" about 10 more records that the first select doesn't return. I can't figure out why. I've confirmed it is returning extra records by removing non nullable fields from the update to see what it actually updates. It sets fields in these extra records to null

update svc_quote_line_import qlimUpdate
set (DESCRIPTION, IS_IMPORTABLE, SERVICE_LINE_NUMBER, COUNT_BASED_TYPE_ID, COUNT_BASED_TYPE_VALUE, ITEM_CATEGORY_ID, part_import_source_id) =

(

                        Select distinct pm.description, 1, prlp.SERVICELINENUMBER, pm.ITEM_COUNT_NAME_ID, pm.ITEM_COUNT_VALUE, ic.ITEM_CATEGORY_ID, 2

                        From svc_quote_import qi, svc_distrib_price_region dpr, svc_quote_line_import qlim, svc_item_categories ic,

                        SVC_SLN_PRODUCT_LINE_GRP_MAP slnpl, partmaster pm, partregionlistprice prlp

			where  qi.quote_import_id = p_QUOTE_IMPORT_ID
			and qi.quote_import_id = qlim.quote_import_id
 			and qlim.ordercode = prlp.ORDERCODE
			and qi.DISTRIBUTORID = dpr.DISTRIBUTORID
			and dpr.REGION_ID = trim(prlp.region_id)
			and dpr.CURRENCYID = prlp.CURRENCY
			and qlim.ordercode = pm.ordercode
			and prlp.SERVICELINENUMBER = slnpl.SERVICE_LINE_NUMBER (+)
			and trunc(prlp.expdate) >= trunc(sysdate)
			and trunc(prlp.msrp_effective_date) <= sysdate
			and trunc(prlp.msrp_expiration_date) >= sysdate
			and pm.portfolioid = 0				 		   		   -- Data and Voice
			and pm.designationflag = ic.ITEM_CATEGORY_DESC (+)
			and qlim.is_importable = 0				   	  	   -- May already be done
			and qlimUpdate.QUOTE_LINE_IMPORT_ID = qlim.QUOTE_LINE_IMPORT_ID
		)
		where qlimUpdate.QUOTE_LINE_IMPORT_ID in


		(

		 	Select distinct qlim.QUOTE_LINE_IMPORT_ID
			From svc_quote_import qi, svc_distrib_price_region dpr,
svc_quote_line_import qlim, svc_item_categories ic,
			SVC_SLN_PRODUCT_LINE_GRP_MAP slnpl, partmaster pm,
partregionlistprice prlp
			where  qi.quote_import_id = p_QUOTE_IMPORT_ID
			and qi.quote_import_id = qlim.quote_import_id
 			and qlim.ordercode = prlp.ORDERCODE
			and qi.DISTRIBUTORID = dpr.DISTRIBUTORID
			and dpr.REGION_ID = trim(prlp.region_id)
			and dpr.CURRENCYID = prlp.CURRENCY
			and qlim.ordercode = pm.ordercode
			and prlp.SERVICELINENUMBER = slnpl.SERVICE_LINE_NUMBER (+)
			and trunc(prlp.expdate) >= trunc(sysdate)
			and trunc(prlp.msrp_effective_date) <= sysdate
			and trunc(prlp.msrp_expiration_date) >= sysdate
			and pm.portfolioid = 0				 		   		   -- Data and Voice
			and pm.designationflag = ic.ITEM_CATEGORY_DESC (+)
			and qlim.is_importable = 0				   	  	   -- May already be done
		);

What makes it stranger is the behaviour seems to be intermittent, i.e. sometimes for the same data it happily updates 49 records. Very confusing

TIA Received on Tue Jul 10 2007 - 08:41:00 CDT