Sometimes you need to update or delete records based on matching records in another table. You may use sub-query for this but there is a better approach by using Joins in your update and delete statements.

Following is an example of how to implement it in your code.

Update d
Set d.Department = Deptt.SEGMENT4_DESCRIPTION
From dimDepartment d
Inner Join Deptt
On d.DepartmentKey = Deptt.SEGMENT4_CODE;

Delete d
From dimDepartment d
Inner Join Deptt
On d.DepartmentKey = Deptt.SEGMENT4_CODE;

In this example, I defined Deptt  as a Synonym. For now, think of Synonym as a reference to the table. The only difference is that the table may reside on another location (server, database, and schema). I will write a separate blog on how to define and use Synonyms.