0

Help Please I am working in Teradata and I get the error message 'Target row updated by multiple source rows', what could be causing this? Here is the code I am using

UPDATE USS_MISC_STAGE.RXA_RX_CLAIM_PRSCRBR_XREF
FROM
(
SELECT DISTINCT
CLAIM_PHARM_ID_RX,
    PAYER_BOX, FILL_DATE, CLAIM_STATUS, PRESCRIBER_ID, PRESCRIBER_FIRST_NAME,
    PRESCRIBER_LAST_NAME, PRESCRIBER_ADDRESS1, PRESCRIBER_ADDRESS2,
    PRESCRIBER_STATE, PRESCRIBER_CITY, PRESCRIBER_ZIP_CODE, PRESCRIBER_PHONE,
    PRESCRIBER_FAX
FROM USS_LOAD_LOG_TABLES.RXA_RX_CLAIM_PRSCRBR_XREF_HIS) RRCPXP
 SET
PRSCRBR_ADDR_LN_1 = PRESCRIBER_ADDRESS1,
PRSCRBR_CITY_NM = PRESCRIBER_CITY,
PRSCRBR_FST_NM = PRESCRIBER_FIRST_NAME,
PRSCRBR_LAST_NM = PRESCRIBER_LAST_NAME,
PRSCRBR_ST_CD = PRESCRIBER_STATE,
PRSCRBR_ZIP_CD = PRESCRIBER_ZIP_CODE 
WHERE USS_MISC_STAGE.RXA_RX_CLAIM_PRSCRBR_XREF.CLAIM_ID  = RRCPXP.CLAIM_PHARM_ID_RX
AND USS_MISC_STAGE.RXA_RX_CLAIM_PRSCRBR_XREF.ADJUD_DT > '2019-05-01'
AND USS_MISC_STAGE.RXA_RX_CLAIM_PRSCRBR_XREF.PRSCRBR_NPI = RRCPXP.PRESCRIBER_ID;
Bond
  • 25
  • 2
  • 13
  • 1
    *what could be causing this?* As the error message implies there are multiple rows matching the Where-condition, i.e. per CLAIM_PHARM_ID_RX/PRESCRIBER_ID. You must apply some logic to get unique source rows, e.g. ROW_NUMBER – dnoeth Jun 19 '20 at 16:46
  • Thanks sir, I will try that, I must admit i've never applied such logic before – Bond Jun 19 '20 at 17:02

0 Answers0