Here's my merge statement:
MERGE PE_TranslationPhrase T
USING PE_TranslationPhrase_Staging S
ON (T.CultureName = S.CultureName AND T.Phrase = S.Phrase)
WHEN MATCHED
THEN UPDATE SET T.TranslationId = T.TranslationId -- do nothing
WHEN NOT MATCHED BY TARGET
THEN INSERT (TranslationId, CultureName, Phrase)
VALUES (S.TranslationId, S.CultureName, S.Phrase);
I seem to be having trouble with the WHEN MATCHED section. Based on this question and answer, I changed the WHEN MATCHED to:
THEN UPDATE SET T.TranslationId = T.TranslationId
But I am still getting this error:
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
I get the reason why. Based on the ON statement, I am getting duplicate matches. But... I don't care. If a record from the source table already exists in the target table, then I want to just do nothing. Don't update anything at all. Skip it.
Is that possible?
If no update is being done, have you considered just doing the insert using rows from PE_TranslationPhrase_Staging that aren't in PE_TranslationPhrase?
CTE Approach:
WITH CTE AS (
SELECT
S.TranslationId,
S.CultureName,
S.Phrase
FROM PE_TranslationPhrase_Staging S
LEFT JOIN PE_TranslationPhrase T on S.Phrase = T.Phrase and S.CultureName = T.CultureName
WHERE T.PHRASE IS NULL
)
INSERT INTO PE_TranslationPhrase (TranslationId, CultureName, Phrase)
SELECT
TranslationId,
CultureName,
Phrase
FROM CTE
Subquery Approach:
INSERT INTO PE_TranslationPhrase (TranslationId, CultureName, Phrase)
SELECT
TW.TranslationId,
TW.CultureName,
TW.Phrase
FROM (
SELECT
S.TranslationId,
S.CultureName,
S.Phrase
FROM PE_TranslationPhrase_Staging S
LEFT JOIN PE_TranslationPhrase T on S.Phrase = T.Phrase and S.CultureName = T.CultureName
WHERE T.PHRASE IS NULL ) TW