UPDATE row when matching row exists in another table

Question

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?

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

Answer 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