更新同一表中的重复记录 [英] Updating duplicate records in same table

问题描述

我有一个名为"Table1"的表
它的数据架构就像这样

ID | col2 | col3 | col4 | col5 | isUnique | dbUnique
1 |红色| 3M | 1995 |英国| 1 | null
2 |红色| 3M | 1995 |英国| 0 |空
2 |红色| 3M | 1995 |英国| 0 |空
3 |红色| 3M | 1995 |英国| 0 |空
4 |粉色| A4 | 2002 |美国| 1 | null
5 |粉色| A4 | 2002 |美国| 0 | null

现在我的问题是，我想更新此表的dbUnique列，以使dbUnique应包含基于col2，col3，col4和amp;的所有重复行中isUnique为1的Table1的ID. col5

所以我的更新数据看起来像这样

ID | col2 | col3 | col4 | col5 | isUnique | dbUnique
1 |红色| 3M | 1995 |英国| 1 | 1
2 |红色| 3M | 1995 |英国| 0 | 1
2 |红色| 3M | 1995 |英国| 0 | 1
3 |红色| 3M | 1995 |英国| 0 | 1
4 |粉色| A4 | 2002 |美国| 1 | 4
5 |粉色| A4 | 2002 |美国| 0 | 4

该表有50,000多行.

如何在MS SQL 2008中做到这一点?

I have a table named "Table1"
Its schema with data is like this

Id | col2 | col3 | col4 | col5 | isUnique | dbUnique
1 | red | 3M | 1995 | UK | 1 | null
2 | red | 3M | 1995 | UK | 0 | null
2 | red | 3M | 1995 | UK | 0 | null
3 | red | 3M | 1995 | UK | 0 | null
4 | pink | A4 | 2002 | USA | 1 | null
5 | pink | A4 | 2002 | USA | 0 | null

Now my question is that, that i want to update dbUnique column of this table, such that dbUnique should contain Id of Table1 where isUnique is 1 in all duplicate rows based on col2,col3,col4 & col5

So my updated data will look like this

Id | col2 | col3 | col4 | col5 | isUnique | dbUnique
1 | red | 3M | 1995 | UK | 1 | 1
2 | red | 3M | 1995 | UK | 0 | 1
2 | red | 3M | 1995 | UK | 0 | 1
3 | red | 3M | 1995 | UK | 0 | 1
4 | pink | A4 | 2002 | USA | 1 | 4
5 | pink | A4 | 2002 | USA | 0 | 4

this table has over 50,000 rows.

How can i do this in MS SQL 2008

推荐答案

如果ID是唯一值，例如:

If Id is unique value, like this:

Id | col2 | col3 | col4 | col5 | isUnique | dbUnique
1 | red | 3M | 1995 | UK | 1 | null
2 | red | 3M | 1995 | UK | 0 | null
3 | red | 3M | 1995 | UK | 0 | null
4 | red | 3M | 1995 | UK | 0 | null
5 | pink | A4 | 2002 | USA | 1 | null
6 | pink | A4 | 2002 | USA | 0 | null

(不是:1、2， 2 ，3、4、5)休假说明:

(not: 1, 2, 2, 3, 4, 5) the fallowing instruction:

SELECT col2, Count(col2) AS CountOfCol2, Min(ID) AS MinOfID
FROM Table1
GROUP BY col2
HAVING (Count(col2)>1);

返回:

returns:

col2 | CountOfCol2 | MinOfID<br />
pink | 2 | 5<br />
red | 4 | 1<br />

因此，您需要构建另一条指令来使用上一条指令返回的数据更新表.
我不确定哪一列是分组列，所以我不能为您提供更多帮助.

So you need to build another instructions to update your table using data returned by previous instruction.
I''m not sure which column is grouping column, so i can''t help you more.

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