Combining duplicate records in SQL Server

Question

I have a table in SQL Server 2012 that holds a list of parts, location of the parts and the quantity on hand. The problem I have is someone put a space in front of the location when they added it to the database. This allowed there to be two records.

I need to create a job that will find the parts with spaces before the location and add those parts to the identical parts without spaces in front of the location. I'm not quite sure where to even start with this.

This is the before:

  Partno  |  PartRev  |  Location  |  OnHand  |  Identity_Column
--------------------------------------------------------------------
  0D6591D    000          MV3         55.000     103939
  0D6591D    000         MV3          -55.000    104618

This is what I would like to have after the job ran:

  Partno  |  PartRev  |  Location  |  OnHand  |  Identity_Column
--------------------------------------------------------------------
  0D6591D    000         MV3         0           104618

Is it just a single white space ? or it can be more than just a single white space — Pரதீப்
– Pரதீப், Commented Feb 1, 2017 at 15:56
Is the "after" row Identity_Column value a mistake? If you're keeping the row without a space it should be 104618, unless something else is going on... — cf_en
– cf_en, Commented Feb 1, 2017 at 16:00

Thorsten Kettner · Accepted Answer · 2017-02-01 16:07:12Z

2

Two steps: 1. update the records with the correct locations, 2. delete the records with the wrong locations.

update mytable
set onhand = onhand + 
(
  select coalesce(sum(wrong.onhand), 0)
  from mytable wrong
  where wrong.location like ' %'
  and trim(wrong.location) = mytable.location
)
where location not like ' %';

delete from mytable where location like ' %';

answered Feb 1, 2017 at 16:07

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thorsten Kettner Over a year ago

Duplicate keys? We are not inserting data, we are not updating keys, we are even deleting records. How can we get such an error then?

Stephen Over a year ago

You may just want to change trim with ltrim or rtrim accordingly

TEC C Over a year ago

The key is a combination of the Part, PartRev, and location by updating the location it creates a duplicate key error since those three would be exactly the same.

Stephen Over a year ago

@TECC how does updating onHand cause duplicate key error?

TEC C Over a year ago

Stephen you are totally correct I read the steps and not the code. I apologize for not reading your answer thoroughly.

Tanner · Accepted Answer · 2017-02-01 16:02:04Z

1

You can do some grouping with a HAVING clause on to identify the records. I've used REPLACE to replace spaces with empty strings in the location column, you could also use LTRIM and RTRIM:

CREATE TABLE #Sample
    (
      [Partno] VARCHAR(7) ,
      [PartRev] INT ,
      [Location] VARCHAR(5) ,
      [OnHand] INT ,
      [Identity_Column] INT
    );


INSERT INTO #Sample
    ([Partno], [PartRev], [Location], [OnHand], [Identity_Column])
VALUES
    ('0D6591D', 000, ' MV3', 55.000, 103939),
    ('0D6591D', 000, 'MV3', -55.000, 104618)
;

SELECT Partno ,
       PartRev ,
       REPLACE( Location, ' ', '') Location,
       SUM(OnHand) [OnHand]
FROM #Sample
GROUP BY REPLACE(Location, ' ', '') ,
         Partno ,
         PartRev
HAVING COUNT(Identity_Column) > 1;

DROP TABLE #Sample;

Produces:

Partno  PartRev Location    OnHand
0D6591D 0       MV3         0

answered Feb 1, 2017 at 16:02

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

1 Comment

TEC C Over a year ago

I think my biggest problem is finding the bad record and finding the matching good record.

Collectives™ on Stack Overflow

Combining duplicate records in SQL Server

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related