带有 WHERE EXISTS 的 SQL UPDATE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9023879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL UPDATE statement with WHERE EXISTS
提问by user1152145
Im trying to write a query that updates a date only if the group im updating has a LINE_CD of 50. Would i do it like this?
我正在尝试编写一个查询，仅当我更新的组的 LINE_CD 为 50 时才更新日期。我会这样做吗？
UPDATE EMPLOYER_ADDL
SET EMPLOYER_ADDL.GTL_UW_APPRV_DT = EMPLOYER_ADDL.DNTL_UW_APPRV_DT
WHERE EXISTS
(
SELECT EMP_PLAN_LINE_INFO.LINE_CD
FROM EMP_PLAN_LINE_INFO
Where EMP_PLAN_LINE_INFO.GR_NBR = EMPLOYER_ADDL.GR_NBR and
EMP_PLAN_LINE_INFO.LINE_CD = 50
)
采纳答案by xbrady
What about this?
那这个呢？
UPDATE ea
SET ea.GTL_UW_APPRV_DT = ea.DNTL_UW_APPRV_DT
FROM EMPLOYER_ADDL ea
INNER JOIN EMP_PLAN_LINE_INFO ei ON(ei.GR_NBR = ea.GR_NBR)
WHERE
ei.LINE_CD = 50
回答by Aaron Bertrand
UPDATE ea
SET GTL_UW_APPRV_DT = DNTL_UW_APPRV_DT
FROM EMPLOYER_ADDL AS ea
WHERE EXISTS
(
SELECT 1
FROM EMP_PLAN_LINE_INFO AS ep
WHERE ep.GR_NBR = ea.GR_NBR
AND ep.LINE_CD = 50
);
However, if you can derive this information from a query, why update the table? Seems like this will have to be run constantly else risk being out of date.
但是，如果您可以从查询中获得此信息，为什么要更新表？似乎这必须不断运行，否则可能会过时。
回答by AJP
I believe this will give you same result.
我相信这会给你同样的结果。
UPDATE ea
SET GTL_UW_APPRV_DT = DNTL_UW_APPRV_DT
FROM EMPLOYER_ADDL AS ea
INNER JOIN EMP_PLAN_LINE_INFO AS ep
ON ep.GR_NBR = ea.GR_NBR
AND ep.LINE_CD = 50
回答by SQLMason
Assuming that GR_NBR is a PK and distinct in EMP_Plan_line_Info:
假设 GR_NBR 是一个 PK 并且在 EMP_Plan_line_Info 中是不同的：
UPDATE EA
SET GTL_UW_APPRV_DT = DNTL_UW_APPRV_DT
FROM EMPLOYER_ADDL EA
INNER JOIN EMP_PLAN_LINE_INFO EP
ON EP.GR_NBR = EA.GR_NBR
AND EP.LINE_CD = 50
回答by Pankaj
Try with this also. I think this is new for you ?
也试试这个。我认为这对你来说是新的？
UPDATE ADDL
SET ADDL.GTL_UW_APPRV_DT = ADDL.DNTL_UW_APPRV_DT
From EMPLOYER_ADDL ADDL
Inner Join EMP_PLAN_LINE_INFO INFO on INFO.GR_NBR = ADDL.GR_NBR
Where INFO.LINE_CD = 50
回答by Brain2000
I believe exists requires a wildcard:
我相信存在需要通配符：
UPDATE EMPLOYER_ADDL
SET EMPLOYER_ADDL.GTL_UW_APPRV_DT = EMPLOYER_ADDL.DNTL_UW_APPRV_DT
WHERE EXISTS
(
SELECT *
FROM EMP_PLAN_LINE_INFO
Where EMP_PLAN_LINE_INFO.GR_NBR = EMPLOYER_ADDL.GR_NBR and
EMP_PLAN_LINE_INFO.LINE_CD = 50
)
I prefer using IN though. Some argue that it can be slower, but I have found the SQL optimizer in 2005 and higher make IN work the same as EXISTS if the field is a non-null field.
不过我更喜欢使用 IN。有些人认为它可能会更慢，但我发现 2005 年及更高版本的 SQL 优化器使 IN 的工作与 EXISTS 相同，如果该字段是非空字段。
UPDATE EMPLOYER_ADDL
SET EMPLOYER_ADDL.GTL_UW_APPRV_DT = EMPLOYER_ADDL.DNTL_UW_APPRV_DT
WHERE EMPLOYER_ADDL.GR_NBR IN
(
SELECT EMP_PLAN_LINE_INFO.GR_NBR
FROM EMP_PLAN_LINE_INFO
Where EMP_PLAN_LINE_INFO.LINE_CD = 50
)
