Update statement while select data from same table

Filed in SQL Leave a comment

UPDATE EmailEvents
SET SubjectES = (SELECT ee.SubjectES FROM EmailEvents ee WHERE ee.Clientid IS NULL AND ee.EmailEventId = EmailEvents.EmailEventId)
WHERE EmailEvents.Clientid = ‘OE’

Key point; you cannot define table alias after Update clause so you have to use complete table name when referring main table columns.

One more example of Self join.
select EmailEventid, Body,clientid,
(SELECT ee.Body FROM EmailEvents ee WHERE ee.Clientid IS NULL AND ee.EmailEventId = EmailEvents.EmailEventId AND ee.Body = EmailEvents.Body )
from EmailEvents
order by emaileventid, clientid

,

TOP