Delete Dup Rows - 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 Dup Rows

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-07-19 : 16:16:53

Hi I have a table like -

auto_id - employee_id - employee_group

Is there a qury that i can run, to check if a employee_id and employee_group have the same content, and if it does, just delete one of the reocrds?

Sample Data
1 - joe1 - cool_group
2 - joe1 - cool_group_1
3 - joe1 - cool_group
4 - joe2 - cool_group_4

After Query Run
1 - joe1 - cool_group
2 - joe1 - cool_group_1
4 - joe2 - cool_group_4

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-19 : 17:04:44

try this:


delete from MyTable 
where auto_id = 
(select 
	max(auto_id)
from MyTable
group by employee_id, employee_group
having count(*) > 1
)

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-07-19 : 17:08:00

Error

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

table name hr_staff_groups
colums, id - employee_id - employee_group

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-19 : 17:13:15

from your error message it looks like that you have several duplicated records with same employee_id and employee_group?

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-07-19 : 17:19:51

yes,
a row can have same employee_id and employee_group
I just want to remove 1 of the records, not both

Sample Data
1 - joe1 - cool_group
2 - joe1 - cool_group_1
3 - joe1 - cool_group
4 - joe2 - cool_group_4

After Query Run
1 - joe1 - cool_group
2 - joe1 - cool_group_1
4 - joe2 - cool_group_4

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-19 : 17:20:13

you can solve this problem by setting the auto_id as identity column.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-19 : 17:21:50

this is exact copy of your data and if works:


create table #hr_staff_groups
(auto_id int
,employee_id varchar(10)
,employee_group varchar(10)
)

insert into #hr_staff_groups
select 1, 'AB','G1' union all
select 2, 'AB','G2' union all
select 3, 'AB','G1' union all
select 4, 'AD','G3' union all
select 5, 'AB','G1' union all
select 6, 'DC','G1'


delete from #hr_staff_groups
where auto_id = 
(select 
	max(auto_id)
from #hr_staff_groups
group by employee_id, employee_group
having count(*) > 1
)

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-07-19 : 17:23:21

The auto ID is set to identity, and PK, its auto increment.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-19 : 17:24:16

so query must work.
is there any other column(s) in the table?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-19 : 17:26:50

[code]-- Setup Data
DECLARE @Foo TABLE (auto_id INT, employee_id VARCHAR(50), employee_group VARCHAR(50))
INSERT @Foo
SELECT 1, 'joe1', 'cool_group'
UNION ALL SELECT 2, 'joe1', 'cool_group_1'
UNION ALL SELECT 3, 'joe1', 'cool_group'
UNION ALL SELECT 4, 'joe2', 'cool_group_4'

-- Delete Duplicates
DELETE
D
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY employee_id, employee_group ORDER BY auto_id) AS RowNum
FROM
@Foo
) AS D
WHERE
RowNum > 1

-- Verify results
SELECT *
FROM @Foo[/code]

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-07-19 : 17:30:10

perfect Lamprey, that worked great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources