Duplicates with merge and select distinct

Question

I'm trying to do an update and an insert using merge but getting duplicates. I need to either update an existing row on table1 or insert it if it does not exist.

Table1:

| col1    |   col2   |   userid  |  col3   |
|---------+----------+-----------+---------|
| string1 | string2  |   user1   | string8 |
| string1 | string4  |   user2   | string5 |
| string1 | string2  |   user3   | string7 |
| string1 | string2  |   user4   | string3 |

Table2:

| type |userid |
|------+-------|
|    a | user1 |
|    g | user1 | 
|    c | user2 |
|    d | user3 |
|    a | user4 | 
|    b | user5 |
|    b | user6 |
|    c | user6 |
|    e | user7 |

SQL:

MERGE INTO table1 t1
USING (SELECT DISTINCT 'string1' AS s1, 'string2' AS s2, userid, 'string3' AS s3
FROM table2 WHERE type in ('a','b','c','d')) src
ON (t1.userid = src.userid AND t1.col2 = src.s2)
WHEN MATCHED THEN 
    UPDATE SET t1.col3 = src.s3 where t1.col2 = 'string2'
WHEN NOT MATCHED THEN INSERT (col1, col2, userid, col3)
    VALUES (src.s1, src.s2, src.userid, src.s3);

The outcome:

|col1     |   col2   |   userid  |  col3   |
|---------|----------|-----------|---------|
| string1 | string2  |   user1   | string3 |
| string1 | string4  |   user2   | string5 |
| string1 | string2  |   user2   | string3 |
| string1 | string2  |   user3   | string3 |
| string1 | string2  |   user4   | string3 |
| string1 | string2  |   user5   | string3 |
| string1 | string2  |   user6   | string3 |
| string1 | string2  |   user6   | string3 |

If I run the select distinct by itself, I get the expected outcome without duplicates but when using it with merge I get a duplicate for user6 that has two rows in t2 matching the where clause. I also tried with group by userid instead of distinct but the outcome was the same.

How do I make this work?

Answer 1

I am not getting any duplicates, I just commented the where clause in update statement as it was giving error to me. Screenshot attached.