问题描述
使用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.
推荐答案
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
------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 ;-)
这篇关于更新具有重复行的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！