SQL CASE with CASE

I have a CASE stmt within CASE and the code is something like below: I need to achieve below condition But below code is not working. Can anyone correct this code?

Here If mgmt.id IS NOT NULL then status_fl should be set as ‘Y’, and If mgmt.id IS NULL then we need to check the condition If staff.No IS NOT NULL THEN update status_fl as ‘Y’ otherwise update status_fl as ‘N’. Thanks in advance.

UPDATE emp SET status_fl= 
WHEN mgmt.id IS NOT NULL THEN 'Y' 
WHEN mgmt.id IS NULL THEN 

    WHEN staff.No IS NOT NULL THEN 'Y' ELSE 'N' END 

FROM EMPLOYEE emp 
LEFT OUTER JOIN Management mgmt on emp.id=mgmt.id 
LEFT OUTER JOIN Staff staff ON staff.No=emp.No

Answer

Is the query below solve your issues ?

UPDATE emp SET status_fl= 
CASE
WHEN mgmt.id IS NOT NULL THEN 'Y' 
WHEN mgmt.id IS NULL and staff.No IS NOT NULL THEN 'Y'
ELSE 'N' END

FROM EMPLOYEE emp 
LEFT OUTER JOIN Management mgmt on emp.id=mgmt.id 
LEFT OUTER JOIN Staff staff ON staff.No=emp.No