如何更新重复记录 [英] How to update duplicate record

问题描述

我有一个表格个人详细信息，其中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

推荐答案

，因为主键违规是异常的允许数据库表中的重复记录。主键的目的是防止这种情况发生，并且正确地防止用户表单多次插入名为PREM SINGH的同一个人。有人试图通过插入这个相同的人但是他的PersonID缺少零来绕过这个主键控制而变得聪明。这会导致数据不正确并损害数据库的完整性。简而言之，您的情况的答案是删除具有错误PersonID的PREM SINGH，并对此愚蠢黑客中的其他类似条目执行相同操作。

您或黑客应该阅读的任何人在数据库设计简介 [ ^ ]。

你最好祈祷其他表中没有其他记录存在链接到此表中具有hacked-PersonID的用户，否则在尝试删除此表中的hacked-PersonID时会遇到外键约束。

Why do you allow duplicate records in a database table. The purpose of the primary key is to prevent such a thing from happening, and it is correct that it is preventing the user form inserting a same person called PREM SINGH more than once. Someone tried to be clever by circumvent this primary key control by inserting this same person but with a missing zero for his PersonID. That results in incorrect data and compromises the integrity of your database. To cut it short, the answer to your situation is to remove that PREM SINGH with a wrong PersonID and do the same to the other similar entries from this dumb hack.
You or whoever the hacker should read up on Introduction to database design[^].
You have better prayed that no other records exist in other tables that link to those users with the hacked-PersonID in this table, else you will run into the foreign key constraint when trying to delete the hacked-PersonID in this table.

看看例子：

Take a look at example:

--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（） [ ^ ]检测哪条记录重复的功能。 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.

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