Multiple where exists

Question

I need to update value_check column in table_work as N based on below conditions

Update value_check as N

1. if employee_id in table_work has no employee_manager_id record (either blank/null or no record at all) in employees_contact table when getdate is between contact_eff_dt and contact_end_dt (join using employee_id)
2. if employee_id in table_work has a record in employees_contact table but employee_manager_id work_location_state is not NJ or NY or ME ..mgr_work_location table has employee_manager_id and work_location_state columns.

I am able to do it with 2 update statements bit is there a simple way to use only one update statement? Maybe using multiple where exists conditions?

Here are the 2 updates I use now:

UPDATE 1

Update work 
set work.value_check = 'N' 
From table_work work 
Where wotk.value_check = 'Y' 
And not exists (select employee_manager_id 
                from employees_contact contact 
                Where contact.employee_id = work.employee_id 
                And getdate() between contact.eff_dt and contact.end_dt)

UPDATE 2

Update work 
set work.value_check = 'N' 
From table_work work 
Join employees_contact contact On contact.employee_id= work.employee_id 
Join mgr_work_location mgr On mgr.mgr_id = contact.employee_manager_id 
Where work.value_check = 'Y' 
And getdate() between contact.eff_dt 
and contact.end_dt And mgr.work_location_state not in ('NJ','NY,'ME')

Answer 1

The first query's NOT EXISTS criteria can probably be emulated in the second query via a LEFT JOIN, and check for contact.employee_id IS NULL (the unmatched)

And those limits on contact.eff_dt can be moved from the WHERE to that LEFT JOIN on mgr_work_location.

A test on rextester here

UPDATE work 
SET work.value_check = 'N' 
FROM table_work work 
LEFT JOIN employees_contact contact 
  ON contact.employee_id = work.employee_id
 AND contact.eff_dt <= getdate()
 AND contact.end_dt >= getdate()
LEFT JOIN mgr_work_location mgr 
  ON mgr.mgr_id = contact.employee_manager_id 
WHERE work.value_check = 'Y' 
  AND (contact.employee_id IS NULL 
       OR mgr.work_location_state NOT IN ('NJ','NY','ME'))