Duplicate keys 2

[danielv]

Does anybody know how it is possible to get duplicate keys in a table? Better yet, does anybody know how to track them down? I have two records with the same keys, and I can't think of any SELECT statement that would find them. Any ideas?

 

[sleipnir214]

It's more than possible. A key only has to be unique if it was specified to be so using the "unique" attribute, or implicitly if the key is a primary key.

To find the duplicates, I'd select a count of them into a scratch table.

For example, if the table in question is named "foo", and the key in question is called "my_field":

create table fubar select my_field, count(my_field) as the_count from foo group by my_field;
select * from fubar where the_count > 1;

______________________________________________________________________
TANSTAAFL!

 

[piti]

without creating an extra table

select key_column from table_name group by key_column having count(key_column) > 1

 

[sleipnir214]

piti,

As a visitor, DanielV can't award you a star, but I will. I had never explored the usefulness of the "HAVING" clause until now. ______________________________________________________________________
TANSTAAFL!

 

[danielv]

Thanks for the responses. I should have been more specific when I asked my original question. The table uses 2 fields as the key. So, Piti, how does that affect the count expression? Would it be--count(field1, field2) or count(field1 AND field2)? Or did I unleash a beast?

 

[piti]

sleipnir214 - we are learning everyday something new, well i do ;-)
thanx for the star

DanielV

this should do the job
select field1, field2 from table_name group by field1, field2 having count(field1) > 1

 

[KarveR]

Pure class, Piti another star for that fine gem. ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[piti]

wow KarveR is giving out stars
have you put out your intention to be on the first place? :-D

 

[KarveR]

Pheer my stars .
______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.