Update field based on join

[StuartBombay]

Help! I'm so close to creating this simple thing. I need to set a boolean in Table A to true based on a date in Table B.

UPDATE message_recipient 
SET is_email_sent = '1'
WHERE EXISTS
(
SELECT * FROM user_message 
INNER JOIN message_recipient
ON message_recipient.message_guid = user_message.guid
)
WHERE user_message.date_sent < '8/3/2008'

I get an error "incorrect syntax near WHERE".
I need this figured out last week (when it broke...). Please help! It works if I leave out the date, but I would like to preserve the most recent if I can.
Thanks.

 

[ethorn10]

Move that last where clause inside the parens.

UPDATE message_recipient 
SET is_email_sent = '1'
WHERE EXISTS
(
     SELECT * FROM user_message 
     INNER JOIN message_recipient
     ON message_recipient.message_guid = user_message.guid
     WHERE user_message.date_sent < '8/3/2008'
)

 

[ousoonerjoe]

You have 2 WHERE clauses. When you need both to be TRUE, then use 'AND'. Unless the 2nd one is intended to be part of the subquery then it needs to be inside the parenthesis.

UPDATE Message_Recipient 
SET IS_Email_Sent = 1
WHERE EXISTS(SELECT * FROM user_message
	INNER JOIN message_recipient
	ON message_recipient.message_guid = user_message.guid)
AND user_message.date_sent < '8/3/2008'

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[StuartBombay]

Doh!
Thank you, ethorn10 - the panic is subsiding.
I start the SQL class fall term, but job is on now!

 

[ethorn10]

Glad I could help.