SQL 当匹配的行存在于另一个表中时更新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1881531/
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
UPDATE row when matching row exists in another table
提问by Val M
I need to update a field on a table to be true only if a matching row exists in another table, for all the rows where the column is currently null in the main table.
仅当另一个表中存在匹配的行时，我才需要将表上的字段更新为真，对于主表中该列当前为空的所有行。
This is a description of what I want to achieve:
这是对我想要实现的目标的描述：
UPDATE [LenqReloaded].[dbo].[Enquiry] A
SET [ResponseLetterSent] = 1
WHERE [ResponseLetterSent] IS NULL
AND EXISTS
(
SELECT * FROM [LenqReloaded].[dbo].[Attachment] B
WHERE A.[EnquiryID] = B.[EnquiryID]
)
This isn't syntactically correct.
这在语法上是不正确的。
I can't code it via an IF EXISTS... statement because I don't have the [EnquiryID] without reading the data from the table.
我无法通过 IF EXISTS... 语句对其进行编码，因为我没有 [EnquiryID] 没有从表中读取数据。
How should I format my UPDATE statement?
我应该如何格式化我的 UPDATE 语句？
采纳答案by AdaTheDev
You weren't far off...
你离得不远...
UPDATE A
SET A.[ResponseLetterSent] = 1
FROM [LenqReloaded].[dbo].[Enquiry] A
WHERE A.[ResponseLetterSent] IS NULL
AND EXISTS ( SELECT * FROM [LenqReloaded].[dbo].[Attachment] B WHERE A.[EnquiryID] = B.[EnquiryID] )
回答by Matt Wrock
You need to use a join in your update:
您需要在更新中使用联接：
UPDATE [LenqReloaded].[dbo].[Enquiry] SET [ResponseLetterSent] = 1
FROM [LenqReloaded].[dbo].[Enquiry] A
join [LenqReloaded].[dbo].[Attachment] B on A.[EnquiryID] = B.[EnquiryID]
WHERE A.[ResponseLetterSent] IS NULL
回答by Charles Bretana
This seems counterintuitive, but you need to establish a table alias in a From clause but use that alias in the Update Clause...
这似乎违反直觉，但您需要在 From 子句中建立表别名，但在更新子句中使用该别名...
Update E Set
ResponseLetterSent = 1
From LenqReloaded.dbo.Enquiry E
Where ResponseLetterSent Is Null
And Exists (Select * From LenqReloaded.dbo.Attachment
Where EnquiryID = E.EnquiryID)
回答by Ray
The thing you are missing is the 'from' clause, which is a t-sql extension - it is the only way to assign an alias to the updated table
您缺少的是“from”子句，它是一个 t-sql 扩展 - 它是为更新的表分配别名的唯一方法
update [lenqreloaded].[dbo].[enquiry]
set [responselettersent] = 1
from [lenqreloaded].[dbo].[enquiry] a
where [responselettersent] is null
and exists (
select *
from [lenqreloaded].[dbo].[attachment] b
where a.[enquiryid] = b.[enquiryid]
)
