Update duplicates in multiple fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update duplicates in multiple fields

Author

Topic

cancer192
Starting Member

10 Posts

Posted - 2009-05-13 : 11:36:32

Hi,

The following query works beautifully if i want to find duplicates in multiple fields:


SELECT a.Field1, a.Field2, a.Field3, a.Field4, a.Field5, a.Field6
FROM TABLE1 AS a INNER JOIN
(SELECT Field1, Field2, Field3, Field4, Field5, Field6
FROM TABLE1 AS TABLE1_1
GROUP BY Field1, Field2, Field3, Field4, Field5, Field6
HAVING (COUNT(*) > 1)) AS b 
ON a.Field1 = b.Field1 AND a.Field2 = b.Field2 AND 
   a.Field3 = b.Field3 AND a.Field4 = b.Field4 AND
   a.Field5 = b.Field5 AND a.Field6 = b.Field6 AND

But how should the query be like if i want to update the DuplicateIndicator field of the duplicate rows? My current query, shown below, is only able to update for rows with duplicate Field1 (just a single field):


UPDATE TABLE1
SET DuplicateInd = '1'
WHERE (Field1 IN
SELECT a.Field1
FROM TABLE1 AS a INNER JOIN
(SELECT a.Field1, a.Field2, a.Field3, a.Field4, a.Field5, a.Field6
FROM TABLE1 AS a INNER JOIN
(SELECT Field1, Field2, Field3, Field4, Field5, Field6
FROM TABLE1 AS TABLE1_1
GROUP BY Field1, Field2, Field3, Field4, Field5, Field6
HAVING (COUNT(*) > 1)) AS b 
ON a.Field1 = b.Field1 AND a.Field2 = b.Field2 AND 
   a.Field3 = b.Field3 AND a.Field4 = b.Field4 AND
   a.Field5 = b.Field5 AND a.Field6 = b.Field6 AND))

How to solve this?

TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:03:19

did you mean this?


UPDATE a
set a.DuplicateInd = 1
FROM  TABLE1 AS a INNER JOIN
(SELECT Field1, Field2, Field3, Field4, Field5, Field6
FROM TABLE1 AS TABLE1_1
GROUP BY Field1, Field2, Field3, Field4, Field5, Field6
HAVING (COUNT(*) > 1)) AS b 
ON a.Field1 = b.Field1 AND a.Field2 = b.Field2 AND 
   a.Field3 = b.Field3 AND a.Field4 = b.Field4 AND
   a.Field5 = b.Field5 AND a.Field6 = b.Field6

cancer192
Starting Member

10 Posts

Posted - 2009-05-13 : 13:06:29

I found a workaround to my problem which is:
(1) create a view with the following query:


SELECT a.ID, a.Field1, a.Field2, a.Field3, a.Field4, a.Field5, a.Field6
FROM TABLE1 AS a INNER JOIN
(SELECT Field1, Field2, Field3, Field4, Field5, Field6
FROM TABLE1 AS TABLE1_1
GROUP BY Field1, Field2, Field3, Field4, Field5, Field6
HAVING (COUNT(*) > 1)) AS b 
ON a.Field1 = b.Field1 AND a.Field2 = b.Field2 AND 
   a.Field3 = b.Field3 AND a.Field4 = b.Field4 AND
   a.Field5 = b.Field5 AND a.Field6 = b.Field6 AND

(2) then create a trigger on TABLE1 for every insert, update and delete:


UPDATE TABLE1
SET DuplicateInd = 1
FROM TABLE1 CROSS JOIN vw_TABLE1_Duplicate
WHERE (TABLE1.ID = vw_TABLE1_Duplicate.ID)

Daily, i need to push >20k records into the database and check for duplicates record. Using this workaround, how will the performance fare? Any drawback of using this method that i'm unaware of?

Appreciate anyone thoughts on this.

TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:19:42

why use trigger for this? cant you just use my solution?

cancer192
Starting Member

10 Posts

Posted - 2009-05-13 : 13:22:27

Uhh i posted tht b4 reading ur reply.
Anyways, ur soln work splendidly.

Thanks a lot!

quote:
Originally posted by visakh16

why use trigger for this? cant you just use my solution?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 10:25:31

welcome

bkleen
Starting Member

5 Posts

Posted - 2010-04-08 : 19:20:07

I have a table that looks like this:

doc_id file_id file_size dup
1 a 1000
2 a 1000
3 a 1000
4 a 1500
5 b 2000
6 b 2000
7 b 2500
8 c 1100
9 c 1100
10 c 1100

I need to find and mark the second, third, etc occurances as dup when the combination of file_id and file_size generate a duplicate. The resulting table would be like this:

doc_id file_id file_size dup
1 a 1000 n
2 a 1000 y
3 a 1000 y
4 a 1500 n
5 b 2000 n
6 b 2000 y
7 b 2500 n
8 c 1100 n
9 c 1100 y
10 c 1100 y

I think the code posted above is close but not sure.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 19:39:17

For bkleen:

CREATE TABLE #tmp (
	doc_id INT,
	[file_id] VARCHAR(10),
	file_size INT,
	dup VARCHAR(10)
)

INSERT INTO #tmp
SELECT 1, 'a', 1000, NULL
UNION ALL SELECT 2, 'a', 1000, NULL
UNION ALL SELECT 3, 'a', 1000, NULL
UNION ALL SELECT 4, 'a', 1500, NULL
UNION ALL SELECT 5, 'b', 2000, NULL
UNION ALL SELECT 6, 'b', 2000, NULL
UNION ALL SELECT 7, 'b', 2500, NULL
UNION ALL SELECT 8, 'c', 1100, NULL
UNION ALL SELECT 9, 'c', 1100, NULL
UNION ALL SELECT 10, 'c', 1100, NULL


UPDATE #tmp 
SET Dup = tu.dup
FROM #tmp ts
INNER JOIN (
		SELECT DISTINCT t1.doc_id, CASE WHEN t2.doc_id IS NULL THEN 'n' ELSE 'y' END AS Dup 
		FROM #tmp t1
		LEFT JOIN #tmp t2
			ON t1.[file_id] = t2.[file_id]
				AND t1.file_size = t2.file_size
				AND t1.doc_id > t2.doc_id ) tu
	ON ts.doc_id = tu.doc_id

SELECT * FROM #tmp 
GO

DROP TABLE #tmp

You'll need to replace #tmp with your own table name.

There are 10 types of people in the world, those that understand binary, and those that don't.

bkleen
Starting Member

5 Posts

Posted - 2010-04-08 : 22:40:55

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-09 : 14:04:24

quote:
Originally posted by bkleen

thank you

it would be better if you can post it as a new thread rather than hijacking other threads as it will improve views and you will get quick solutions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 14:40:22

quote:
Originally posted by visakh16
it would be better if you can post it as a new thread rather than hijacking other threads as it will improve views and you will get quick solutions

It wasn't really a hijack. His problem was almost identical to the OP. I can't see a problem with him posting his question here.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-10 : 07:14:38

quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16
it would be better if you can post it as a new thread rather than hijacking other threads as it will improve views and you will get quick solutions

It wasn't really a hijack. His problem was almost identical to the OP. I can't see a problem with him posting his question here.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Even then posting it as new thread will certainly increase the number of views as well as chance of getting quick solution. I suggested it for OP's own sake. I never told posting here was a problem.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources