Avatar of dmissel
dmissel
Flag for United States of America asked on

Getting a unique constraint on an update to a table

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

Oracle Database

Avatar of undefined
Last Comment
Naveen Kumar

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Naveen Kumar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dmissel

ASKER
GREAT
Naveen Kumar

Good and thanks.
Your help has saved me hundreds of hours of internet surfing.
fblack61