CODEWITHSUNDEEP
sqlsql-serverdatabasesql-update
K.Tom
K.Tom

Reputation: 185

How to update and delete duplicate in SQL Server?

I have two table Add (Parent table) and AddXref (Child Table) and want to delete duplicate records in parent table and also update in Child table. In AddXref table the two records should have anyone of AddId from Add Table.i Tried with basic update statement but it didn't worked. How can I update AddXref Table?

My Attempt: I have written the query up to this point, but am stuck on what to do next

Update AddXref name
SET a.AddId=b.AddId
FROM AddXref a
INNER JOIN Add b ON a.AddId = b.AddId
WHERE b.AddId = ( Select Top 1 ax.Addid from Add ax
                  INNER JOIN Add ax1  on
ax.AddressMain = a.AddressMain and
              ax.city = a.city and
              ax.Country = a.Country and
              ax.State = a.State and
              ax.ZipCode = a.ZipCode);

| AddId  | AddressMain  | City    | Country | State   | ZipCode    |
|--------|--------------|---------|---------|---------|------------|
| 8CA25D | 1234 Main St | Antioch | USA     | Florida | 60002-9714 |
| 5T7YTR | 1234 Main St | Antioch | USA     | Florida | 60002-9714 |

| AddXrefId | AddressId | IndividualId | InstitutionId |
|-----------|-----------|--------------|---------------|
| 822145    | 8CA25D    | 7652ER       | NULL          |
| 435902    | 5T7YTR    | NULL         | NA1043        |


| AddXrefId | AddressId | IndividualId | InstitutionId |
|-----------|-----------|--------------|---------------|
| 822145    | 8CA25D    | 7652ER       | NULL          |
| 435902    | 8CA25D    | NULL         | NA1043        | --Output either "8CA25D" or "5T7YTR" any one of it

Upvotes: 0

Views: 769

Answers (1)

AJP
AJP

Reputation: 2125

first gather duplicate data.

select AddressMain, City, Country, State, ZipCode, min(addId) as minID, Max(addId) as MAxID
INTO #tmpData
from add
group by AddressMain, City, Country, State, ZipCode

second update child table

Update AddXref 
SET a.AddId=b.MaxID
FROM AddXref a
INNER JOIN #tmpData b ON a.AddId = b.MinID -- you can use maxId too if you want.

third remove duplicate record from parents.

DELETE FROM Add where AddId IN (Select MinID FROM #tmpData)

This code is not tested.

Upvotes: 1

Related Questions