Finding Duplicate information.

[EM1107]

Good morning everyone!

Hi need some help!

I am having problem findint the duplicate information in a table.

The table I have as 3 columns. All fields are integer fields.

The problem I have is that none of the column as a primary key.

I wanted to find out if I was having duplicate information in the table. The way I did that was by creating a temporary table with the same configuration as the original table and adding an index on it that disregard the duplicate. I found 169 entry in it.

The problem I have is that I can not take the new list and replace the old list with it.

The table description is as follow.

Col 1 represent an objects ID
Col 2 represent a user or group.
Col 3 represent the security apply to that user or group on the object.

My filter was created on col 1 and 2 as they should be unique and col 3 could be different. If I use the new table and replace the content of the old table with it, I may handup changing the security of the object.

How can I compate the two to return only the duplicated information?

 

[nigelrivett]

try

select col1, col2, col3, count(*)
from tbl
group by col1, col2, col3

to see what is duplicated.
The you can delete the duplicated rows

set rowcount 1
select 1
while @@rowcount > 0
delete t1
from tbl t1
join (select col1, col2, col3
from tbl
group by col1, col2, col3
having count(*) > 1)
t2
on t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3

set rowcount 0

Run it in a transaction first to see what it deletes.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nread]

Try :

SELECT field1, field2, field3
FROM
table1
WHERE field1+field2+field3
IN(
SELECT field1+field2+field3,count(1)
FROM
table1
HAVING count(1) > 1
GROUP by field1, field2, field3)

Ta