Remove duplicates and update IDs linked to nonduplicates

Question

I have two tables, tableA and tableB, linked through a locationID.

TableA has descriptive survey info with each record from a different time (ie unique), while TableB has purely locational information. However, there are lots of duplicates in TableB, yet each has a unique locationID, which has an entry in TableA. I've found plenty posts about removing duplicates from TableB, but how can I update the locationIDs in TableA so they are linked to the unique locations in TableB once duplicates are removed...

Help much appreciated!

Answer 1

When you find a duplicate, you know at least two locationIDs. Therefore, before you delete from TableB, you can update TableA:

UPDATE TableA
SET locationID = foundLocationID1
WHERE locationID = foundLocationID2

Alternatively, if you have a whole group you're about to delete at once:

UPDATE TableA
SET locationID = foundLocationID1
WHERE locationID IN (foundLocationID2, foundLocationID3, foundLocationID4)

Answer 2

First of all you have to update TableA before removing the duplicates in TableB. If not, you are gonna lose information.

After that, make all the duplicates in table B equal in one field (let's say LocationDescription)

Then, select one LocationID of all them. Let´s say the MIN Location ID.

After that, update tableA with that MinID and then remove from TableB the duplicates in a really easy way (with a NOT in).

Here´s an example:

Asumming

TableA:                                 Table B

Location ID                             LocationId  LocationDescription
   1                                       1        Neuquen
   2                                       2        Cipolletti
   3                                       3        Neuquen
   4                                       4        Cipolletti
   5                                       5        Neuquen

UPDATE TableA
SET locationID=TABLEBAUX.UniqueID
FROM TableA 
INNER JOIN 
(
SELECT UniqueID, LocationID
FROM 
(SELECT MIN(LocationID) as UniqueID,LocationDescription
FROM TableB
GROUP BY LocationDescription) TEMP
INNER JOIN TableB
ON TEMP.LocationDescription=TABLEB.LocationDescription) TABLEBAUX
ON TableA.LocationID=TABLEBAUX.LocationID

DELETE FROM TableB 
WHERE LocationID NOT in (SELECT LocationID FROM TABLEA)

After this you get this:

TableA:                                 Table B

Location ID                             LocationId  LocationDescription
   1                                       1        Neuquen
   2                                       2        Cipolletti
   1                                       
   2                                       
   1