如何从重复的记录中更新记录 [英] 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屋!