Getting a unique constraint on an update to a table

dmissel
dmissel used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Production Manager / Application Support Manager
Commented:
try :

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)
GROUP BY  EMPLID,
COMPANY,
BALANCE_ID,
EMPL_RCD,
SPCL_BALANCE,
ERNCD
HAVING COUNT(*) =1;

Author

Commented:
GREAT
Naveen KumarProduction Manager / Application Support Manager

Commented:
Good and thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial