How can I check if theres a duplicate record in my SQl table/Database, What kind of querry I can execute to check this problem..

-- Anonymous, July 19, 2004

Answers

Melvin,

It depends on what you mean by duplicate. So I will have to choose an example.

Let's say that you want to know if there is a duplicate author in the authors table in the pubs database. (Here I choose to define duplicate as having the same last name and same initial of the first name. Similar queries result if you define duplicate differently.)

Here are three queries you can run that will list the duplicate (which is Anne Ringer, id # 899-46-2035).

select au_id, au_lname, au_fname from authors a where au_lname in (select au_lname from authors b where a.au_lname = b.au_lname and substring (a.au_fname, 1, 1) = substring (b.au_fname, 1, 1) and a.au_id < b.au_id)

select a.au_id, a.au_lname, a.au_fname from authors a join authors b on a.au_lname = b.au_lname and substring (a.au_fname, 1, 1) = substring (b.au_fname, 1, 1) where a.au_id < b.au_id

select min (au_id), min (au_lname), min (substring (au_fname, 1, 1)) from authors group by au_lname, substring (au_fname, 1, 1) having count (*) > 1

Hope this helps,

Eric

-- Anonymous, July 19, 2004

select count(*),au_id from authors group by au_id having count(*) > 1;

-- Anonymous, July 22, 2004