Post reply

Delete and merge duplicate records from joined tables?

  • October 21, 2014 at 2:12 pm

    #291189

    Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?

    SELECT

    a1z103acno AccountNumber

    , a1z103frnm FirstName

    , a1z103lanm LastName

    , a1z103ornm OrgName

    , a3z103adr1 AddressLine1

    , A3z103city City

    , A3z103st State

    , A3z103zip Zip

    , a6z103area AreaCode

    , a6z103phon PhoneNumber

    , a8z103mail Email

    FROM

    proddta.fz103a1 WITH (NOLOCK)

    INNER JOIN proddta.fz103a2 WITH (NOLOCK)

    ON a1z103acno = a2z103acno

    INNER JOIN proddta.fz103a3 WITH (NOLOCK)

    ON a2z103adid = a3z103adid

    AND a2z103actv = 'Y'

    AND a2z103prim = 'Y'

    LEFT OUTER JOIN proddta.fz103a5 WITH (NOLOCK)

    ON a1z103acno = a5z103acno

    AND a5z103actv = 'y'

    AND a5z103prim = 'Y'

    INNER JOIN proddta.fz103a6 WITH (NOLOCK)

    ON a5z103phid = a6z103phid

    LEFT OUTER JOIN proddta.fz103a8 WITH (NOLOCK)

    ON a1z103acno = a8z103acno

    AND a8z103actv = 'Y'

    AND a8z103prim = 'Y'

  • October 22, 2014 at 3:41 am

    #1753976

    We don't know how your tables or data look like.

    See the first article linked in my signature line and discover how to post effectively and get replies faster.

    Moreover, NOLOCK means "no transactional consitency", it's not a go-faster button. It will return inconsistent data, duplicate data or skip row altogether.

    -- Gianluca Sartori

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply