Remove duplicate records

Question

Post reply

Remove duplicate records

Sanket-267266

SSC-Addicted

Points: 418
More actions
April 4, 2006 at 6:54 am

#167798

Hi,
I have one table named "tblUsers" it has no primary key defined and no id field only "username" is one varchar field, now i have 20 rows so 20 username are in this table and many of them are duplicate. so how can i remove those duplicate records for one table which has only one column like username
can anyone has sql query for this? if anyone has faced such problem
Thanks
Sanket
Regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

"select distinct username"..should remove the duplicates...

but what kind of a table is this that has only one column ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Also forgot to mention that you can set an unique constraint on a column where you want to avoid duplicates...still curious about that table though...

**ASCII stupid question, get a stupid ANSI !!!**

Sanket-267266 SSC-Addicted Points: 418 More actions · Answer 3

Let me specify in more details....

Table Name: tblName

Column Name: id,Name

Some rows from this table:

id Name

----------------

1 Sanket

2 Sachin

3 Kishan

4 Sanket

5 Ram

6 sachin

now I want to fire one query which will delete all duplicate records like sanket, sachin and next select * output would be like this

id Name

----------------

1 Sanket

2 Sachin

3 Kishan

5 Ram

Regards

Vee Hall of Fame Points: 3028 More actions · Answer 4

Hoping to solve the problem,

declare @tblusers table( uid int ,uname varchar(25) )

insert into @tblusers

select 1, 'Sanket'

UNION ALL

select 2, 'Sachin '

UNION ALL

select 3, 'Kishan'

UNION ALL

select 4, 'Sanket'

UNION ALL

select 5, 'Ram'

UNION ALL

select 6, 'sachin'

UNION ALL

select 7, 'sachin'

UNION ALL

select 8, 'Sanket'

SELECT * FROM @TBLUSERS

SELECT * FROM @TBLUSERS O WHERE UNAME IN ( SELECT UNAME FROM @TBLUSERS WHERE UID<O.UID AND UNAME=O.UNAME)

DELETE FROM @TBLUSERS WHERE UID IN (

SELECT UID FROM @TBLUSERS O WHERE UNAME IN ( SELECT UNAME FROM @TBLUSERS WHERE UID<O.UID AND UNAME=O.UNAME)

)

SELECT * FROM @TBLUSERS

Sanket-267266 SSC-Addicted Points: 418 More actions · Answer 5

That's great and thanks for your work.

I have one question....

Can I manage whole process by one query? if it is so then how can I write such query by managing joins

Regards

Andreas AJV SSC Eights! Points: 800 More actions · Answer 6

Suppose that we have the following data in a table named Table1.

id Name

----------------

1 Sanket

2 Sachin

3 Kishan

4 Sanket

5 Ram

6 sachin

We see that the name Sanket occurs 2 times:

id Name

----------------

1 Sanket

4 Sanket

First of all you have to decide which of the above ids to delete. The 1 or 4? ... etc.

If you dont mind then you can execute the folowing query:

delete from table1 where id not in

(select max(id) from table1 group by name);

IMPORTANT ASSUMPTION: The IDs are unique.... .. can u tell me why?

------------
When you 've got a hammer, everything starts to look like a nail...

Sanket-267266 SSC-Addicted Points: 418 More actions · Answer 7

Great man... that's what I needed.

And regarding your assumption: ID is Primary key so you can see unique.

Regards

Andreas AJV SSC Eights! Points: 800 More actions · Answer 8

.... any time.

------------
When you 've got a hammer, everything starts to look like a nail...