Home > SQL > Update statement while select data from same table

Update statement while select data from same table

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

,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Trackbacks:0

Listed below are links to weblogs that reference
Update statement while select data from same table from extremeweblog.com
TOP