更新具有重复行的表 [英] Update table with duplicate rows

问题描述

使用MSSQL 2000.我有一个需要更新重复行的表。我有类似表格的新价值。

表A：

UID MD_ID类型

12 11796 30

13 11796 31

11 11796 48

11 11556 302

42 11556 305

11 11552 146



表B：

UID MD_ID类型

22 11556 312

23 11556 315

12 11552 113

11 11796 50

11 11796 45

41 11796 48



需要使用表B更新表A的值。我在网上看到使用CTE的人，但我不清楚，因为我是SQL新手。

表B可能或者可能没有与表A相同的行数，如果它的值数量较少，我可以保留原始值，如果它有更多的值，那么我可以跳过额外的值。

两个表中的UID都不匹配，因此不能使用UID来匹配值。

Using MSSQL 2000. I have a table with duplicate rows that needs to be updated. I have new values with similar table.
Table A:
UID MD_ID Type
12 11796 30
13 11796 31
15 11796 48
18 11556 302
42 11556 305
18 11552 146

Table B:
UID MD_ID Type
22 11556 312
23 11556 315
12 11552 113
15 11796 50
18 11796 45
41 11796 48

Need to update values of Table A using Table B. I see people on internet using CTE but it's not clear to me as I am new to SQL.
Table B may or may not have same number of rows as Table A, if it has less number of values, I can leave the original value as it is and if it has more values then I can skip extra values.
UID in both tables doesn't match so cannot use UID to match value.

推荐答案

如果你是ave一个包含重复行的表，你只能更新它们，因为没有办法区分它们：根据重复的定义：



你可以做的是添加一个标识列到表 - 它确实区分了记录，因为它永远不会重复（即使记录被删除）。现在记录可以单独区分和访问。



但是 - 有趣的部分 - 如果记录是重复的，你需要摆脱多余的价值 - 或者在至少决定如何选择您计划访问的那个。您可以使用的一种策略是，您只对具有IDENTITY字段的MAX（）值的任何一个组感兴趣。

If you have a table with duplicate rows you can only update both as there's no way to distinguish them: by definition of being duplicates:

What you can do is add an identity column to the table - which does distinguish the records as it will never duplicate (even if a record is deleted). Now the records can be distinguished and accessed individually.

But - the fun part - if the records are duplicates, you need to get rid of the redundant values - or at least decide how you'll pick the one you plan on accessing. One strategy you may use is that you're only interested in the one of any group with the MAX() value of the IDENTITY field.

如果行基于UID是唯一的，并且我正确理解了您的问题，您可以使用如下查询：

If the rows are unique based on UID and I understood your question correctly, you can use a query like:

UPDATE TableA 
SET Type = (SELECT TableB.Type
            FROM TableB
            WHERE TableB.UID = TableA.UID) -- actual matching criteria for tables?
WHERE NOT EXISTS (SELECT 1 
                  FROM TableB
                  WHERE TableB.UID = TableA.UID -- actual matching criteria for tables?
                  AND TableB.Type > TableA.Type) -- or any other proper conditions

当然需要根据您的要求进行修改

That of course needs modification as per your requirements

在while循环中检查下面的代码以供参考。

check below code in while loop for reference.

------sample table with data
drop table #tableA
drop table #tableB
create table #tableA ([UID] bigint, MD_ID bigint, [Type] bigint)
create table #tableB ([UID] bigint, MD_ID bigint, [Type] bigint)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(12,11796,30)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(13,11796,31)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(15,11796,48)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(18,11556,302)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(42,11556,305)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(18,11552,146)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(22,11556,312)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(23,11556,315)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(12,11552,113)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(15,11796,50)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(18,11796,45)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(41,11796,48)
drop table #tableC


--------save into temperory table #tableC.values of md_id , new type,uid (uid default as zero) for matched rows in both tables
SELECT DISTINCT   #tableB.MD_ID,#tableB.Type,0 AS UID  INTO #tableC FROM #tableA 
INNER JOIN #tableB ON #tableA.MD_ID= #tableB.MD_ID
group by #tableB.MD_ID, #tableA.MD_ID,#tableA.Type,#tableB.Type
having count(#tableA.MD_ID) =count(#tableB.MD_ID)


DECLARE @di_MD_ID BIGINT
DECLARE @di_Type BIGINT
DECLARE @di_UID BIGINT


While (Select Count(UID) From #tableC where UID=0 ) > 0
Begin
	-------------select first row values of #tableC Where UID=0
	SELECT	TOP	1	@di_MD_ID=MD_ID,	@di_Type=type  	FROM	#tableC	WHERE	UID=0
	---------------update table A---------------------------------------
	UPDATE	TOP	(1)	#tableA	SET	type =@di_Type	WHERE	(MD_ID=@di_MD_ID	AND	Type!=@di_Type)	AND
							UID NOT IN (SELECT UID FROM #tableC WHERE MD_ID=@di_MD_ID )and type!=@di_Type
	-------------select UID of last updated						
	SELECT @di_UID=uid	FROM #tableA	WHERE MD_ID=@di_MD_ID AND type=@di_Type
	-------------Update first row values of #tableC Where UID=0
	UPDATE	#tableC	SET  UID =@di_UID	WHERE	MD_ID=@di_MD_ID and type=@di_Type and UID=0

END 
 
 SELECT * FROM #tableA

祝你好运; - ）

good luck ;-)

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