合并 SQL Server 中的重复记录 [英] Combining duplicate records in SQL Server

问题描述

我在 SQL Server 2012 中有一个表格，其中包含零件列表、零件位置和手头数量.我遇到的问题是有人在将位置添加到数据库时在位置前面放了一个空格.这允许有两个记录.

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.

这是之前:

  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

推荐答案

两个步骤:1. 更新位置正确的记录，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 ' %';

这篇关于合并 SQL Server 中的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！