troubleshooting Question

Getting a unique constraint on an update to a table

Avatar of dmissel
dmisselFlag for United States of America asked on
Oracle Database
3 Comments1 Solution527 ViewsLast Modified:
I am getting a unique constraint on the following update

UPDATE PS_EARNINGS_BAL A
SET BALANCE_QTR = (SELECT Max(balance_qtr)
                      FROM ps_check_ytd B
                    WHERE B.EMPLID = A.EMPLID
                    AND B.COMPANY = A.COMPANY
                    AND B.BALANCE_ID = 'CY'
                    AND B.BALANCE_YEAR = 2011) ,
BALANCE_PERIOD = (SELECT Max(balance_PERIOD)
                    FROM ps_check_ytd B
                    WHERE B.EMPLID = A.EMPLID
                    AND B.COMPANY = A.COMPANY
                    AND B.BALANCE_ID = 'CY'
                    AND B.BALANCE_YEAR = 2011)
WHERE A.BALANCE_ID = 'CY'
AND A.BALANCE_YEAR = 2011
AND A.ERNCD = 'CTS'
AND SPCL_BALANCE = 'Y'
AND EXISTS (SELECT 'X' FROM PS_CHECK_YTD B
              WHERE  B.EMPLID = A.EMPLID
                    AND B.COMPANY = A.COMPANY
                    AND B.BALANCE_ID = 'CY'
                    AND B.BALANCE_YEAR = 2011);



The keys to the EARNINGS_BAL table are
Emplid
Company
Balance_id
Empl_rcd
Spcl_balance
Erncd

The keys to the CHECK_YTD table are
EMPLID
COMPANY
BALANCE_ID

The keys to the CHECK_YTD table is
EMPLID
COMPANY
BALANCE_ID
BALANCE_YEAR
BALANCE_QTR
BALANCE_PERIOD



A previous script executed 1000 of the following inserts
INSERT INTO PS_EARNINGS_BAL VALUES('9999913731','001','CY',2011, 3,7,0,'Y','CTS',0,0,0,110513.2    ,0,0);

I changed the UPDATE sql into following select statement
SELECT EMPLID,
COMPANY,
BALANCE_ID,
EMPL_RCD,
SPCL_BALANCE,
ERNCD ,
COUNT(*)
FROM PS_EARNINGS_BAL  A
WHERE A.BALANCE_ID = 'CY'
AND A.BALANCE_YEAR = 2011
AND A.ERNCD = 'CTS'
AND SPCL_BALANCE = 'Y'
AND EXISTS (SELECT 'X' FROM PS_CHECK_YTD B
              WHERE  B.EMPLID = A.EMPLID
                    AND B.COMPANY = A.COMPANY
                    AND B.BALANCE_ID = 'CY'
                    AND B.BALANCE_YEAR = 2011)
GROUP BY  EMPLID,
COMPANY,
BALANCE_ID,
EMPL_RCD,
SPCL_BALANCE,
ERNCD
HAVING COUNT(*) >1;

The above SQL returned the following row
EMPLID         COMPANY BALANCE_ID EMPL_RCD SPCL_BALANCE ERNCD COUNT(*)
1000144358 CMI          CY                 0                Y                       CTS       2

I would like to bypass this record int the update without hardcoding the emplid as the sql will be run
many times and needs to be generic

Thanks in advance
Dennis

ASKER CERTIFIED SOLUTION
Naveen Kumar
Production Manager / Application Support Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros