Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19

    Unanswered: stuck on UPDATE query using table aliases

    Hi All

    I have a table called "tblINBOUND_EMAILS" which contains the following two fields:
    CAMPAIGN_ID (this is blank and I need to use UPDATE to populate it)
    FROM (this contains an email address)

    And a second table called "tblRESEARCH_CONTACTS" which contains the following two fields:
    EMAIL (this contains an email address)
    CAMPAIGN_ID (this is the update value I want to use)

    Requirement:
    Update tblINBOUND_EMAILS.CAMPAIGN_ID where FROM = tblRESEARCH_CONTACTS.EMAIL
    but only where tblRESEARCH_CONTACTS count is equal to 1

    I can't figure how to do it using query designed so I tried writing it. This is what I have but it's pretty hopeless:

    Code:
    UPDATE tblINBOUND_EMAILS
        SET tblINBOUND_EMAILS.[CAMPAIGN_ID]=T2.[CAMPAIGN_ID]
    FROM
    	(
    		SELECT tblRESEARCH_CONTACTS.[CAMPAIGN_ID], T1.[EMAIL]
            FROM 
    			(
    				SELECT tblRESEARCH_CONTACTS.[EMAIL], Count(tblRESEARCH_CONTACTS.[EMAIL]) AS COUNT_EMAIL
    				FROM tblRESEARCH_CONTACTS
    				GROUP BY tblRESEARCH_CONTACTS.[EMAIL]
    				HAVING (((Count(tblRESEARCH_CONTACTS.[EMAIL]))=1))
    			)  AS T1 
    			INNER JOIN
                    tblRESEARCH_CONTACTS 
                    ON 
                    T1.[EMAIL] = tblRESEARCH_CONTACTS.[EMAIL]
                GROUP BY tblRESEARCH_CONTACTS.[CAMPAIGN_ID], T1.[EMAIL]
    	) AS T2
    INNER JOIN 
    	tblINBOUND_EMAILS
        ON  
    	T2.[EMAIL]=tblINBOUND_EMAILS.[FROM];
    Help greatly appreciated.
    Last edited by Jon von der Heyden; 01-30-14 at 07:09.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    whats the relevance of the count being at least 1?

    as I read yoiur query you want to update tblINBOUND_EMAILS with the campaign ID from tblRESEARCH_CONTACTS where the email addresses match
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19
    Sometimes the email addresses are repeated in other records in the tblRESEARCH_CONTACTS table. Where this is the case the same individual (email address) might be assigned to multiple campaign ID's. If the email address only occurs once in the table field then it can only pertain to a single campaign ID so where this is the case I am 100% certain that I will be updating tblINBOUND_EMAILS with the correct campaign_ID.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    presuably your update is where
    the emails match
    AND the campaign id in the inbound emails is null
    AND the count of campaigns for that id in reserch contacts is 1
    ..how does the emial getinto the inbound table?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19
    Quote Originally Posted by healdem View Post
    presuably your update is where
    the emails match
    AND the campaign id in the inbound emails is null
    AND the count of campaigns for that id in reserch contacts is 1
    Yes on all parts except "count of campaigns for that id in research is 1"... Strictly speaking you are right but the way it is written in rather "count of emails in research for the inbound email is 1".

    Quote Originally Posted by healdem View Post
    ..how does the emial getinto the inbound table?
    The emails in inbound are collected from a MS Outlook folder. The application records every email in the inbox and then moves it to an archive folder. Each email pertains to a campaign, I need to identify as many of those campaigns as possible. Some email addresses belong to multiple campaigns. Where that is the case the email address count in the research table will be greater than 1 (and hence cannot ID the campaign using this method, and will require manual campaign allocation).

  6. #6
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19
    It turns out the problem is the grouping. As soon as I use grouping it becomes read-only. It seems is the only alternative is to build a temp table to use that in my update.

    Thanks for looking.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •