Deleting rows with duplicate 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

SQL Server 2000 Forums

SQL Server Development (2000)

Deleting rows with duplicate fields

Author

Topic

fizgig
Starting Member

34 Posts

Posted - 2002-01-15 : 05:20:56

I have a table like this;

id (nummeric, primary, not null)
name (varchar)
e-mail (varchar)

I want to remove the rows with duplicate e-mail values, but because the name field can vary i cannot use DISTINCT and put that in a temporary table and put it back.

sona
Yak Posting Veteran

68 Posts

Posted - 2002-01-15 : 05:36:30

Delete from emailtable where id= ANY
(select emailtable.Id,name,E-mail from emailtable LEFT JOIN
(select min(id),email from emailtable groupby e-mail) A
on emailtable.id=A.Id Where A.ID is NULL
)

fizgig
Starting Member

34 Posts

Posted - 2002-01-15 : 05:37:11

query is not completely correct.

i get this error: No column was specified for column 1 of 'A'.

Edited by - fizgig on 01/15/2002 05:44:18

Nazim
A custom title

1408 Posts

Posted - 2002-01-15 : 07:49:33

Read this Article by Graz

http://www.sqlteam.com/item.asp?ItemID=3331

----------------------------------
"True love stories don't have endings."

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-15 : 07:59:58

It's also in the FAQ

Damian

fizgig
Starting Member

34 Posts

Posted - 2002-01-15 : 08:11:33

But that's for duplicate rows dudes! My question is about some duplicate columns.

quote:

It's also in the FAQ

Damian

fizgig
Starting Member

34 Posts

Posted - 2002-01-15 : 08:12:30

The important thing is, can someone fix the invalid sql query ?

quote:

But that's for duplicate rows dudes! My question is about some duplicate columns.

quote:

It's also in the FAQ

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-15 : 08:17:06

You could modify the article to suit though.

As for the query, the problem is min(id) needs an alias.

Something like this :
(select min(id) as ID ,email from emailtable group by e-mail)

Damian

Nazim
A custom title

1408 Posts

Posted - 2002-01-15 : 08:27:09

How about this one

delete from tablename
where id not in (
select min(id) stt from tablename
group by emailaddress)

didnt tested it.

----------------------------------
"True love stories don't have endings."

sona
Yak Posting Veteran

68 Posts

Posted - 2002-01-16 : 06:59:23

Nazim's query is working fine.Great yaa..

I think i beated around the bush for that simple one.

Still have to learn a lot........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources