How Does Merge Work
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How Does Merge Work

  1. #1
    Join Date
    Sep 2001
    Location
    Atlanta, GA
    Posts
    7

    Question

    I am getting ORA-02291 (integrity constraint on the foriegn key, parent key does not exist) error when running the following MERGE statement from within a PL/SQL Block within a script. I am converting an extensive amount of data from an 8i database to a new datamodel on a 9i database. The error is bogus. The parent key does exist. I've already inserted into the table in question using the same foriegn key. But when I do it with the merge it fails. cPropLst is a cursor.

    MERGE INTO Properties.PropListPaidFor pl
    USING ( SELECT a.PropertyID PropertyID, tma.IsUtility IsUtility, SUM(tma.Provided) Provided
    FROM apartments.Amenities@ag a, Properties.tmp_MapAmenities tma
    WHERE a.PropertyID = cPropLst.PropertyID AND
    a.Available = 'Y' AND
    tma.OldAmenityNumber = a.AmenityNumber AND
    tma.IsUtility <> 0 AND
    OldAmenityNumber < 32
    GROUP BY a.PropertyID, tma.IsUtility ) s
    ON ( pl.PropertyID = s.PropertyID AND pl.ListingID = cPropLst.ListingID AND pl.UtilityTypeID = s.IsUtility )
    WHEN MATCHED THEN UPDATE SET pl.IsSubsidized = (pl.IsSubsidized + s.Provided)
    WHEN NOT MATCHED THEN INSERT ( pl.PropertyID, pl.ListingID, pl.UtilityTypeID, pl.IsSubsidized )
    VALUES ( cPropLst.PropertyID, cPropLst.ListingID, s.IsUtility, s.Provided );


    I tried commiting all changes before executing this code, added the cursor identifiers (pl) to the INSERT and a few other things to no avail. I don't know what the source of this error is. If I try to execute it manually, replacing the variables with values, it works fine.

    Can anyone help?
    CMerc

  2. #2
    Join Date
    Sep 2001
    Location
    Atlanta, GA
    Posts
    7

    Red face

    For those who care;

    Apparently the INSERT/VALUES portion of a MERGE INTO statement does not like expressions or variables such as the Cursor variable. It must have replaced the values of the two cursor variables I was using with something else (no idea what else)giving the bogus Foreign key error when it tried to insert them. I'd love to know what it's doing if anyone out there knows please let me know.

    When I placed the cursor variables into the USING clause and placed the column names into the VALUES area it worked fine.

    Just thought that someone out there would want to know.
    CMerc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width