[SOLVED] How to update source table when matched or not matched?

How to update source table when matched or not matched?

I have 2 table, my goal is when I upload file(the file contain more than one data) I already insert file data in table history, the first step is I want to compare history table to table called EDC table if an uploaded file contains data that exist in EDC then update history table (history table contain column isExist) if matched I want to update history and if not matched I want to update column isExist to 0

MERGE EDC AS trgt
USING (SELECT kcndh.Nomor_Kartu_Kredit,kcndh.Nama_CardHolder, kcndh.IsExistEDW FROM History kcndh WHERE kcndh.nama_file = '1233.csv')S
ON trgt.Nomor_Kartu_Kredit = Nomor_Kartu_Kredit AND trgt.Nama_Pemegang_Kartu = Nama_CardHolder
WHEN MATCHED THEN
    UPDATE SET
        S.IsExistEDW = 1
WHEN NOT MATCHED THEN
    UPDATE set
        S.IsExistEDW = 0;

I got error

An action of type 'UPDATE' is not allowed in the 'WHEN NOT MATCHED' clause of a MERGE statement.

Is there any way to compare and update without using merge or how do I achieve my goal without comparing upload value in history table one by one to EDC table?

Solution

As already pointed out in the comments, you can only update the target table with a MERGE statement (not the source table).

However, you can do this with a simple UPDATE statement (there is no need for a MERGE statement):

UPDATE History SET IsExistEDW=CASE WHEN EXISTS (
        SELECT * FROM EDC e
        WHERE e.Nomor_Kartu_Kredit = h.Nomor_Kartu_Kredit 
        AND e.Nama_Pemegang_Kartu = h.Nama_CardHolder
    ) THEN 1 ELSE 0 END
FROM History h
WHERE h.nama_file = '1233.csv'