drew.allen - Friday, March 24, 2017 2:04 PM

earlysunrirse - Friday, March 24, 2017 11:12 AM

Hi,
try the code below::

update dbo.User_table
Set Department = case
when y.Department = w.DeptCode then w.DeptReportName
else NULL
END
from dbo.User_table y inner join New_Department w on y.Department=w.Deptcode

Regards
Anna

This uses two different instances of dbo.User_Table: one aliased "y" and the other unaliased.  This may not (and probably WILL not) produce the results that you are expecting.

Also, the CASE expression is unnecessary in this statement, because the join conditions are exactly the same as the CASE conditions, so only the first condition will ever be met.  The CASE expression would still be unnecessary even if you changed the JOIN to a LEFT OUTER JOIN.

Drew