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

    Unanswered: TSQL - battling with alias

    Hi All

    This:
    Code:
    use nsdb
    UPDATE tblINBOUND_EMAILS
    SET tblINBOUND_EMAILS.CAMPAIGN_ID = tblTEMP.[CAMPAIGN_ID]
    FROM tblTEMP
    LEFT JOIN tblTEMP ON 
    tblINBOUND_EMAILS.[FROM] = tblTEMP.[EMAIL]
    WHERE tblINBOUND_EMAILS.[CAMPAIGN_ID] is null
    I get the following error:
    The objects "tblTEMP" and "tblTEMP" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
    I have tried using aliases but I just can't crack the syntax.

    Can anyone point out the correction required?

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    UPDATE inb
    SET inb.CAMPAIGN_ID = tmp.[CAMPAIGN_ID]
    FROM tblINBOUND_EMAILS as inb
    INNER JOIN tblTEMP as tmp 
        ON inb.[FROM] = tmp.[EMAIL]
    WHERE 
        inb.[CAMPAIGN_ID] is null
    Hope this helps.

  3. #3
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19
    Wow! Yes that works! I was nearly there but overlooked the required change to the ON statement.

    Much obliged!

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Putting “tbl-” on a table name is called tibbling and we do not do that. We do not name a table “Temp”-- that is a generic description of how the table is used, not what it means in a valid data model. Likewise “email” is generic!

    We do not mix data and meta-data. You need a course in data modeling.

    Why do you think that “from” is a valid attribute name? NO! It is a preposition, not a noun!

    We do not use the old Sybase UPDATE.. FROM.. syntax. It does not work! Google this! We have ANSI/USO Standard MERGE statement:

    MERGE INTO Inbound_Emails
    USING (<<your temp table expression>>) AS Source
    ON Inbound_Emails.campaign_id = Source.campaign_id
    WHEN NOT MATCHED
    THEN INSERT
    VALUES Source.email_address;

    Since we have no DDL, this is a guess and skeleton.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Jon:

    Celko means well, and he REALLY knows SQL. He just gets cranky when he encounters code that he's been trying to teach SQL users to avoid for years. Don't let his presentation put you off, he really means well and does know what he's talking about (at least most of the time ).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19
    I'm not put off. On the contrary there is a lot of food for thought and it's clear to me that Celko knows his stuff. In hind sight I would have used a totally different naming convention and much of what I have has been inherited.

    However, I have to take baby steps here and learn 1 thing at a time (I'm a slow learner, must be my age catching me up!)

    What I'm working with is legacy queries from MS Access and we recently upsized to SQL. And alot of what we have can do with re-writing. For instance, the SQL above is part of a more complex routine that was written in three segments because Access would not support the complete routine.

    1. Create a temporary table:
    Code:
    selection into tblTemp
    from
     select tblresearch_contacts.campaign_id, t1.email
     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
    2. and then I run the update:
    Code:
    update inb
    set inb.campaign_id = tmp.campaign_id
    from tblinbound_emails as inb
    inner join tbltemp as tmp 
        ON inb.[from] = tmp.[email]
    where
        inb.[campaign_id] is null
    3. and then finally drop the temp table:
    Code:
    drop table tbltemp
    I've started to play with the merge but I'm having all sorts of syntax errors. It's not liking my "temp table expression" #1 above. But I'm going to keep trying...

  7. #7
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19
    If I may I would like to enlist some more help to implement the MERGE statement suggested by Celko.

    Please excuse terrible column and table names (I do intend on changing these but at the moment the knock-on effects would be too profound to deal with).

    Table1:
    tblResearch_Contacts
    columns:
    campaign_id (int)
    email (nvarchar = email address)

    Table2:
    tblInbound_Emails
    columns:
    campaign_id (int)
    from (nvarchar = email address)

    I wish to identify all email addresses from tblResearch_Contacts where the email address pertains to a single campaign_if only. Essentially this will be the case where the email address exists only once.

    Then I wish to update tblInbound_Emails with the campaign_id from the above, where the email addresses match (i.e. from = email), but only where campaign_id in tblInbound_Emails is null (I don't want to update the campaign_id where it has already been updated).

    I have attempted the following:
    Code:
    merge tblinbound_emails as target
    using
    	( 
    	 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 source
    on target.[from] = source.email
    when matched by target then
    	insert(campaign_id)
    	values(source.campaign_id);
    But this is full of syntax errors and I don't think I understand the match properly...

    Help very gratefully appreciated.

  8. #8
    Join Date
    Jun 2012
    Location
    Greyton, South Africa
    Posts
    19
    The first very stupid mistake was attempting insert when clearly it should be 'update'... whoops!
    Code:
    merge tblinbound_emails as target
    using
    	( 
    	 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 source
    on target.[from] = source.email
    when matched then
    	update set target.campaign_id = source.campaign_id;
    I believe this works - but have yet to test some more (no syntax errors at least)...

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Without DDL, I cannot be sure but the Source looks too complex. Let the MERGE's USING.. ON.. do this work. Why do all that work to get rid of dups? Here is a guess:

    MERGE Inbound_Emails AS Target
    USING (SELECT DISTINCT campaign_id, something_email
    FROM Research_Contacts AS RC
    GROUP BY RC.something_email) AS Source
    ON Target.something_email = Source.something_email
    WHEN MATCHED
    THEN UPDATE SET Target.campaign_id = Source.campaign_id;

Posting Permissions

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