Updating Target table when the data is not available in Source

Question

In sql I want to update a table when the data is not present in source.

Target Table

Source Table

so now for EmpId 1 is not having courseid 3 in source I need to update it need to change isthere column to "no". I tried a basic left join but it is not working. Can you please help with the command to Update it with an Update ot with Merge it should modify other rows.

Result I want Resultant Target Table

The left join I tried

update tableA set isthere = "no" from tableA left jon tableB on tableA.EmpId = tableB.EmpId and tableA.CourseId = tableB.courseId where tableB.CourseId is null -- this is not working offcourse chainging other rows too

and

update tableA set isthere = "no" from tableA left jon tableB on tableA.EmpId = tableB.EmpId and tableA.CourseId = tableB.courseId where tableB.CourseId is null and tableA.EmpId = tableB.EmpId -- this is not all working as tableB.EmpId will be null

need help with update or merge statement

Answer 1

You can find below the answer using SQL SERVER 2022. I used both update and merge statement. If you need additional information because you don't understand something, please ask me.

update  t
set isthere ='NO'
FROM target t LEFT JOIN source s ON t.empid=s.empid and t.courseid=s.courseid
WHERE s.EMPID IS NULL AND s.COURSEID IS NULL AND s.ISTHERE IS NULL

merge into target t
using source s on(t.empid=s.empid and t.courseid=s.courseid)
when not matched by source 
then update set t.isthere='NO';