Delete and merge duplicate records from joined tab

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delete and merge duplicate records from joined tab

Author

Topic

crassdummy
Starting Member

1 Post

Posted - 2014-10-21 : 15:58:04

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'
1
sourceeditrollbacklink
asked 10 mins ago

Chuck
1
Delete and merge duplicate records from joined tables?
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'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 10:50:48

try using DISTINCT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-10-25 : 15:42:53

without some sample data its hard to understand what according to you are duplicates. Please give required info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources