0

I have a table Users

+----------+----------+
|  ID_EMP  | isActive |
+----------+----------+
|   100    +    1     +
|   101    +    1     +
+----------+----------+

and a table UsersInfo

+----------+------------+
|  ID_EMP  |   FRDATE   |
+----------+------------+
|   100    +   null     +
|   101    + 2019-10-10 +
+----------+------------+

I tried to update column Users.isActive with 0 when the UsersInfo.FRDATE is not null. I read similar questions and ended up with the following query, which is not working as expected, as it is updating all the columns with 0

UPDATE [dbMyDB].[dbo].[Users]
SET [dbMyDB].[dbo].[Users].[isActive] = 0
FROM [dbMyDB].[dbo].[Users] USERS
INNER JOIN
    [dbMyDB].[dbo].[UsersInfo] INFO
ON USERS.ID_EMP = INFO.ID_EMP
WHERE EXISTS 
(
    SELECT FRDATE FROM [dbMyDB].[dbo].[UsersInfo]
    WHERE FRDATE IS NOT NULL
)

what I am missing? Thank you!

1
UPDATE users 
SET isActive=0 
FROM users 
JOIN userinfo 
    ON users.id_emp = info.id_emp 
   AND frdate IS NOT NULL

?

  • Thanks it worked! It was simpler that I thought :) – yaylitzis Oct 7 at 7:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.