UPDATE row when matching row exists in another tab

问题:

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.

How should I format my UPDATE statement?

回答1:

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] )

回答2:

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

回答3:

This seems counterintuitive, but you need to establish a table alias in a From clause but use that alias in the Update Clause...

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)

回答4:

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

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]
    )