SQL Server : UPDATE with 2 inner joins

Question

I'm trying to update a Contacts_List table with the last activity date which in the Opened table. So I am doing an inner join for the Opened table with another table which has a common column and then doing another inner join with the contacts list. I'm not sure why but i keep getting the following error.

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'inner'.

Please help!

My code:

UPDATE [dbo].[Contacts_List]
SET [LAST_ACTIVITY_DATE] =  MAX(Opened.EvntCptrdDt)
WHERE [LAST_ACTIVITY_DATE] != MAX(Opened.EvntCptrdDt)
INNER JOIN [dbo].[Intrctn] R (nolock) ON Opened.IntrctnSK = R.IntrctnSK 
INNER JOIN [dbo].[Contacts_List] C (nolock) ON R.CustEmailID = C.Email_id 
GROUP BY Opened.IntrctnSK, R.IntrctnSK, R.EmailAdrText

the other question posted is for a single inner join..im not sure how to do 2 inner joins in an UPDATE statement

You need a `FROM` clause but I'm not sure what you are doing with the `WHERE` clause — S3S, May 15 '18 at 18:48
Possible duplicate of [How do I UPDATE from a SELECT in SQL Server?](https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) — Tab Alleman, May 15 '18 at 18:48
BE VERY CAREFUL with that nolock hint. It is far more sinister than most people realize. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ Read carefully the part about index corruption when using nolock in an update. — Sean Lange, May 15 '18 at 19:18

score 0 · Answer 1 · edited May 15 '18 at 19:20

You have to set a from clausule for the update with join, but in this case u can do it from a variable, you declare it and then update the value from the variable

select @variableUpdate = max(Opened.EvntCptrdDt), @variableWhere  = max(Opened.EvntCptrdDt) from Contacts_List join Intrctn R (nolock) on Opened.IntrctnSK = R.IntrctnSK  join Contacts_List C (nolock) on R.CustEmailID = C.Email_id 
where LAST_ACTIVITY_DATE != max(Opened.EvntCptrdDt)
 group by Opened.IntrctnSK, R.IntrctnSK, R.EmailAdrText

update [dbo].[Contacts_List]set [LAST_ACTIVITY_DATE] = @variableUpdate where [LAST_ACTIVITY_DATE] !=@variableWhere

score 0 · Answer 2 · answered May 15 '18 at 18:51

try something like this

UPDATE [dbo].[Contacts_List]
SET [LAST_ACTIVITY_DATE] =  MAX(Opened.EvntCptrdDt)
FROM [Contacts_List]
INNER JOIN Opened
ON ...........
INNER JOIN [dbo].[Intrctn] R (nolock) 
ON Opened.IntrctnSK = R.IntrctnSK 
INNER JOIN [dbo].[Contacts_List] C (nolock) 
ON R.CustEmailID = C.Email_id

score 0 · Answer 3 · answered May 15 '18 at 18:55

UPDATE CL
SET [LAST_ACTIVITY_DATE] =  MAX(Opened.EvntCptrdDt)
FROM [dbo].[Contacts_List] CL
INNER JOIN [dbo].[Intrctn] R (nolock) ON CL.Opened.IntrctnSK = R.IntrctnSK 
AND R.CustEmailID = CL.Email_id 
WHERE [LAST_ACTIVITY_DATE] != MAX(Opened.EvntCptrdDt)
--GROUP BY Opened.IntrctnSK, R.IntrctnSK, R.EmailAdrText

I think you don't need the GROUP BY.

SQL Server : UPDATE with 2 inner joins

3 Answers3