Delete duplicates - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Delete duplicates

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-03-06 : 10:28:21

Trying to delete duplicate records from a table. Following script returns this error:
Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


DELETE 
FROM imrefdes_sql
WHERE ID 
IN(select a.imrefdes_pkg,a.imrefdes_doc_type, a.imrefdes_parent_item, a.imrefdes_item_no,
a.imrefdes_ord_type,a.imrefdes_ord_no,a.imrefdes_seq_no, a.imrefdes_note
from imrefdes_sql as a,imrefdes_sql AS b
where a.imrefdes_pkg=b.imrefdes_pkg And a.imrefdes_doc_type=b.imrefdes_doc_type 
AND a.imrefdes_parent_item=b.imrefdes_parent_item and a.imrefdes_item_no = b.imrefdes_item_no
and a.imrefdes_ord_type = b.imrefdes_ord_type and a.imrefdes_ord_no = b.imrefdes_ord_no and a.imrefdes_seq_no = b.imrefdes_seq_no and
a.imrefdes_note = b.imrefdes_note)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 10:38:01

You are saying WHERE ID IN () but inside the SELCT you are selecting a lot of coumns...just select the column which corresponds to ID

that shud do it.

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-03-06 : 10:42:40

I replaced ID with imrefdes_note and receive the same message. Is there a better way to remove duplicate records?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 14:27:41

Provide sample data and expected output so we can help you.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 14:54:01

Which

a.imrefdes_seq_no

do you want to keep after removing duplicates? Min or Max?Clarify

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-03-06 : 14:55:44

See this post for what I expect to get and sample data:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121177

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-06 : 16:57:27

You didn't reply to Sodeep's question. Going by the sql you have written, there might be multiple imrefdes_seq_no against imrefdes_ord_no. If thats not duplicate to you and you just want to insert the distinct combinations of all those columns, you just need this.

select distinct 	a.imrefdes_pkg,a.imrefdes_doc_type, a.imrefdes_parent_item, a.imrefdes_item_no,
	a.imrefdes_ord_type,a.imrefdes_ord_no,a.imrefdes_seq_no, a.imrefdes_note into dist_values

from 
	imrefdes_sql as a


GO

truncate table imrefdes_sql

GO

insert into imrefdes_sql
select * from dist_values

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources