I tried to address the inserts and updates with sample data as you mentioned it did not work.

Final_table

INSERT INTO final_table (id, pid, code, date) VALUES
(1, 101, 'A001', '2023-11-01'),
(2, 102, 'B001', '2023-11-02'),
(3, 103, 'C001', '2023-11-03'),
(4, 104, 'D001', '2023-11-04');

Source_table

INSERT INTO source_table (id, pid, code) VALUES
(2, 102, 'B002'),  -- Matching record, code needs to be updated
(5, 105, 'E001'),  -- New record, needs to be inserted
(3, 103, 'C002');  -- Matching record, code needs to be updated

Merge

MERGE INTO final_table AS T
USING source_table AS S
ON T.pid = S.pid

-- For updates
WHEN MATCHED AND (
    T.code IS DISTINCT FROM S.code  -- Update only if 'code' differs
) THEN
    UPDATE
    SET 
        T.code = S.code 

-- For inserts
WHEN NOT MATCHED THEN 
    INSERT (id, code, pid)
    VALUES (S.id, S.code, S.pid);

Result