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

On Jul 10, 2:55 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Jul 10, 9:41 am, zzzzz..._at_hotmail.com wrote:
>
>
>
> > 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
>
> What is the exact version of Oracle? 10.x.x.x
>
> What does the explain plan show as to how the queries are being
> solved?
>
> There have been bugs on various versions of 10g where wrong results
> could be returned by a query where a FBI was involved and where hash
> aggregation was used. If, this is a bug my guess is it is related to
> a problem with IN cluases. I do not know of one but it is likely to
> be version specific and perhaps platform specific as well.
>
> Can you recognize any repeatable pattern about the data when the query
> fails as compared to when it works?
>
> HTH -- Mark D Powell --

Version is oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

I don't see a pattern. It is consistently works for a while then consistently fails. I've identified the records the second select should not return but when run as part of the Update does return. I've verified they should not be returned from the select. I'm loath to say it's an oracle bug as we've just gone to 10g so I'm new to 10g but the SQL has been working on 9i for a couple of years.

I feel if I re-engineer the update (perhaps first writing the source records for the update into a table then doing the update) I can avoid the issue. Problem then is that update syntax is used elsewhere so perhaps I can't have confidence in it...

Perhaps someone could suggest an alternative update syntax to update one table from a set of tables where not all records that can be updated will have a match in the set of tables and only records that do have a match should get updated? Received on Tue Jul 10 2007 - 09:18:25 CDT