none
Please help me to use EXISTS in place of INNER join.. RRS feed

  • Question

  • Hi,
    I have a source table with three fields 'EmpID', 'StateCode' and 'StateName'....Eg: 1001, 'NY' ,'NEWYORK'. It has millions of records.

    I have a target table with only two columns - State_Code and State_Name which is loaded by getting the distinct values of Statecodes and StateNames from Source table.

    Everyday, I have to compare the source & target table based on StateCode key and update the 'State_name' in Target table if there is a change in the State_Name.

    Eg: In target table, there is a record like Statecode='NJ' and StateName'Neweee_Jerseyy' but in the source table, the record is Statecode='NJ' and StateName'NewJersey',

    then the target table has to be updated correctly as Statecode='NJ' and StateName'New_Jersey'.

    For this I used the below query 

    UPDATE T
    SET 
    T.STATE_NAME=S.STATENAME
    FROM 
    DBO.TARGET (NOLOCK) T 
    INNER JOIN DBO.SOURCE S ON 
    T.STATE_CODE=S.[STATE_CODE] 
    AND T.STATE_NAME<>S.STATENAME

    and its working fine - but my  Manager told me to use EXISTS, which I am not so sure how to use it - i tried the below but its not working..can anyone please help me on this?

    UPDATE [DBO].[target] t
           STATE_NAME=s.STATE_NAME
    FROM   [DBO].[source] s (NOLOCK) 
    WHERE STATE_NAME<>STATENAME  
          AND  EXISTS (  
          SELECT 1  
          FROM [dbo].[target] t (NOLOCK)  
      WHERE  t.state_CODE=s.statecode   
           )  
    Friday, June 14, 2019 4:57 AM

All replies

  • Hi Revathy Menon,

     

    Thank you for your issue.

     

    In your original script, I found that you would like to update target table and set target.STATE_NAME=source.STATE_NAME . So I think inner join is more suitable for your example .

     

    Or you would like to check that if you need to update?

     
    if exists (select 1 from DBO.TARGET T 
    where exists (select 1 from DBO.SOURCE S where  
    T.STATE_CODE=S.[STATE_CODE] 
    AND T.STATE_NAME<>S.STATENAME))
    
    UPDATE T
    SET 
    T.STATE_NAME=S.STATENAME
    FROM 
    DBO.TARGET (NOLOCK) T 
    INNER JOIN DBO.SOURCE S ON 
    T.STATE_CODE=S.[STATE_CODE] 
    AND T.STATE_NAME<>S.STATENAME

    If I have any incorrect understanding , please let me know.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 14, 2019 7:03 AM
  • Try this MERGE statement:

    Merge DBO.TARGET T 
    Using DBO.SOURCE S ON T.STATE_CODE=S.[STATE_CODE] 
    When matched AND ISNULL(T.STATE_NAME,'')<>ISNULL(S.STATENAME,'') Then
    UPDATE 
    SET  STATE_NAME=S.STATENAME;
    

    Friday, June 14, 2019 2:11 PM
    Moderator
  • Hi,

    Try this:

    UPDATE T
    SET T.STATE_NAME=S.STATENAME
    FROM DBO.TARGET (NOLOCK) T 
    where exists (select 1 from source a where a.state_code = t.state_code and a.state_name <> t.state_name)

    Mark as answer if it is helpful. Thanks.

    Saturday, June 15, 2019 3:25 AM