主页 / dba / 问题 / 250406
Accepted
yaylitzis
Asked: 2019-10-07 22:38:44 +0800 CST

根据另一个表的列值更新列

  • 772

我有一张表用户

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

和一个表UsersInfo

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

当不为空时，我尝试Users.isActive用 0更新列。UsersInfo.FRDATE我阅读了类似的问题并以以下查询结束，它没有按预期工作，因为它正在更新所有列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
)

我缺少什么？谢谢！

sql-server update

1 个回答

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

    ？

    • 1

相关问题