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: Weird problem: Update statement updating records it should not

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

From: sybrandb <sybrandb_at_gmail.com>
Date: Tue, 10 Jul 2007 07:49:10 -0700
Message-ID: <1184078950.759479.319700@p39g2000hse.googlegroups.com>


On Jul 10, 4:18 pm, zzzzz..._at_hotmail.com wrote:
> 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?- Hide quoted text -
>
> - Show quoted text -

The statement is incorrect anyway as anything between the () in an IN statement constitutes a SET, and SETS are unique *by definition*. So the distinct is redundant.
However you probably have duplicates in that column in 'table1', which is why you are updating more records than you expect.

--
Sybrand Bakker
Senior Oracle DBA
Received on Tue Jul 10 2007 - 09:49:10 CDT

Original text of this message

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