Update only when not exists

Question

I am trying to update table tbl1 using table tbl2 but only if combination tbl2 (tbl2_col1,tbl2_col2) rows don't exist in tbl1. In this case I want to update 000006 both row to match those in tbl2. I am getting an error on the query:

Violation of PRIMARY KEY constraint 'PK__tbl1__A1B90E2508A9C3D2'. Cannot insert duplicate key in object 'dbo.tbl1'. The duplicate key value is (000006, GG1).

What am I missing?

tbl2 :

tbl2_col1  tbl2_col2
000002     B
000003     C
000004     D
000005     Z
000006     GG1
000006     YY

tbl1:

tbl1_col1  tbl1_col2
000002     B
000003     C
000004     D
000005     Z
000006     GG
000006     YY

Query 1:

UPDATE tbl1 SET
    tbl1_col2 = s.tbl2_col2
FROM tbl2 s
WHERE tbl1.tbl1_col1 = s.tbl2_col1
AND NOT EXISTS (
    SELECT 1
    FROM tbl1
    WHERE tbl1_col2 = s.tbl2_col2 AND tbl1_col1 = s.tbl2_col1
)

Answer 1

As already mentioned, it is best to try it with a select first, to see what will be the outcome.
You can use a right join for this

select t1.tbl1_col1,
       t1.tbl1_col2,
       tbl2.tbl2_col1,
       tbl2.tbl2_col2
from   tbl1 t1
  right join tbl2 on t1.tbl1_col1 = tbl2.tbl2_col1
                 and t1.tbl1_col2 = tbl2.tbl2_col2 
where  t1.tbl1_col1 is null

this will get you this result, which actually are all rows in tbl2 that you don't have in tbl1

tbl1_col1	tbl1_col2	tbl2_col1	tbl2_col2
null	null	000006	GG1

So from here we can easily create our update statement, because we can simply use the query above in our update statement.

update t1
set    t1.tbl1_col2 = t.tbl2_col2
from   tbl1 t1
  join ( select --t1.tbl1_col1, 
                --t1.tbl1_col2,
                tbl2.tbl2_col1,
                tbl2.tbl2_col2
         from   tbl1 
           right join tbl2 on tbl1.tbl1_col1 = tbl2.tbl2_col1
                          and tbl1.tbl1_col2 = tbl2.tbl2_col2 
         where  tbl1.tbl1_col1 is null
       ) t on t1.tbl1_col1 = t.tbl2_col1

and after that tbl1 will look like this

tbl1_col1	tbl1_col2
000002	B
000003	C
000004	D
000005	Z
000006	GG1
000006	GG1

I also made a DBFiddle here

EDIT

However, if you need this result

tbl1_col1	tbl1_col2
000002	B
000003	C
000004	D
000005	Z
000006	GG1
000006	YY

then change the update query to this
(The only difference is the additional where clause at the end)

update t1
set    t1.tbl1_col2 = t.tbl2_col2
from   tbl1 t1
  join ( select tbl2.tbl2_col1,
                tbl2.tbl2_col2
         from   tbl1 
           right join tbl2 on tbl1.tbl1_col1 = tbl2.tbl2_col1
                          and tbl1.tbl1_col2 = tbl2.tbl2_col2 
         where  tbl1.tbl1_col1 is null
       ) t on t1.tbl1_col1 = t.tbl2_col1
where not exists ( select 1 
                   from   tbl2 
                   where  tbl2.tbl2_col1 = t1.tbl1_col1
                   and    tbl2.tbl2_col2 = t1.tbl1_col2
                 )

And again, here is a DBFiddle