如何更新重复记录 | 如何更新重复记录

本文介绍了如何更新重复记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个表格个人详细信息，其中personUID是

Hi,

I have a table personal detail where personUID is

PRIMARY Key

PersId		PersonUID    	PersonnelName
137		01066141L	PREM SINGH
128		02679425F	RADHAKRISHANAN
129		1066141L	PREM SINGH
130		04457802P	MOTI SINGH
135		2365847P        Ramesh

在此表中，我想更新小于9位的personuid列。在开始时添加零，但除了那些已经添加零的内容，如Prem singh。

我尝试过：

我尝试了一个简单的更新查询，但由于Prem singh

In this table I want to update that personuid column which is less than 9 digit. Add with zero in starting but except those no which is already add with zero like Prem singh .

What I have tried:

I have try a simple update query but exception is primary key violation due to Prem singh

推荐答案

--create variable - type of table
DECLARE @tmp TABLE(PersId INT, PersonUID VARCHAR(9) PRIMARY KEY, PersonnelName VARCHAR(30))

--insert sample data into table
INSERT INTO @tmp (PersId, PersonUID, PersonnelName)
VALUES(137, '01066141L', 'PREM SINGH'),
(128, '02679425F', 'RADHAKRISHANAN'),
(129, '1066141L', 'PREM SINGH'),
(130, '04457802P', 'MOTI SINGH'),
(135, '2365847P', 'Ramesh')

--update data
UPDATE t1 SET PersonUID = RIGHT('000000000', 9-LEN(t1.PersonUID)) + t1.PersonUID
FROM @tmp AS t1 INNER JOIN
(
	SELECT PersId, PersonUID, ROW_NUMBER() OVER(PARTITION BY RIGHT('000000000', 9-LEN(PersonUID)) + PersonUID ORDER BY LEN(PersonUID) DESC) AS PID, PersonnelName
	FROM @tmp
) AS t2 ON t1.PersId = t2.PersId AND t2.PID = 1

--show data after apdate
SELECT *
FROM @tmp

结果：

Result:

137	01066141L	PREM SINGH
128	02679425F	RADHAKRISHANAN
129	1066141L	PREM SINGH   --skipped
130	04457802P	MOTI SINGH
135	02365847P	Ramesh       --added leading zeros

我希望上面的例子有助于理解如何更新你的数据。

我用过 []检测哪条记录重复的功能。 ROW_NUMBER（）函数返回结果集分区中行的序号，从1开始，每个分区的第一行。

I hope that above example is helpful in understanding how to update your data.

I have used ROW_NUMBER()[^] function to detect which record is duplicated. ROW_NUMBER() function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

这篇关于如何更新重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！