如何从重复的记录中更新记录 [英] How to update a record from the duplicate records

问题描述

大家好，

以下是我的Employee表.我只想更新employee表中的扩展名.如何更新表格.请回复我.

Device_ID扩展名Emp_ID Network_ID
-------------------------------------------------- -------
SEP6C626DBF2486 20016 EN74 74 MyNetworkID
SEP6C626DBF2486 20016 EN74 74 MyNetworkID

Hello all,

Below is my Employee table. I want to update only extension from the employee table. How can i update the table. Please reply me.

Device_ID Extension Name Emp_ID Network_ID
---------------------------------------------------------
SEP6C626DBF2486 20016 EN74 74 MyNetworkID
SEP6C626DBF2486 20016 EN74 74 MyNetworkID

推荐答案

如果您使用的是sql 2005，请检查以下代码以查找重复数据，并相应地更新/删除数据.

Please check the following code to find the duplicate data if you are using sql 2005 and update/delete data accordingly.

DECLARE @Duplicate TABLE (
ID INT,
FNAME VARCHAR(10),
MNAME VARCHAR(10)
)

INSERT INTO @Duplicate VALUES(1, ‘AAA’,'CCC’)
INSERT INTO @Duplicate VALUES(2, ‘BBB’,'DDD’)
INSERT INTO @Duplicate VALUES(1, ‘AAA’,'CCC’)
INSERT INTO @Duplicate VALUES(2, ‘BBB’,'DDD’)
INSERT INTO @Duplicate VALUES(1, ‘AAA’,'CCC’)
INSERT INTO @Duplicate VALUES(2, ‘BBB’,'DDD’)
INSERT INTO @Duplicate VALUES(3, ‘BCB’,'DGD’) 

--FOR SQL SERVER 2005 and above
;WITH CTE as(
SELECT ROW_NUMBER() OVER(PARTITION BY ID, FName, MName ORDER BY (SELECT 1)) AS RowID,
*
FROM @Duplicate
)
SELECT ID, FName, MName
FROM CTE
WHERE RowID = 1

参考:
http://blog.sqlauthority.com/2007/03/01 /sql-server-delete-duplicate-records-rows/ [

Reference:
http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/[^]

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