SQL DELETE 3 column arguments - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL DELETE 3 column arguments

Author

Topic

marek
Starting Member

34 Posts

Posted - 2009-12-09 : 10:51:29

Hallo

TAB:

Numcompany.....Numemploy......branch
10 100 250
10 100 250
15 800 500
15 800 400
15 800 400
22 400 500
22 700 900
22 700 005

RESULT:

Numcompany.....Numemploy......branch
10 100 250
15 800 500
15 800 400
22 400 500
22 700 900
22 700 005

I need help please. I have 3 columns and I need to delete the rows that contain the same data in Table, with 1 record must remain in the table.

Big thanks for help. Marek

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-09 : 11:06:25

marek
which ones must remain and which ones must go what is your criteria for which stays and which goes. also why are they in there in the first place?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-09 : 11:07:42

Have you tried searching this site for "delete dups" ?

Be One with the Optimizer
TG

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-09 : 11:23:11

[code]with t1 as (select *, row_number() over (partition by Numcompany, Numemploy, branch order by Numcompany) as row from MyTable)
delete from t1 where row > 1[/code]

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

marek
Starting Member

34 Posts

Posted - 2009-12-09 : 12:54:56

Thanks Ryan Randall

. . . I Need to delete duplicate rows
a..b..c
1--2--3
1--2--3
2--5--6
2--4--6
2--5--6
2--3--1
3--2--1
2--3--1
result
a..b..c
1--2--3
2--5--6
2--4--6
2--3--1
3--2--1

Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-09 : 13:07:53

What are all the columns in your table? You need something to distinguish one dupe row from another. There is no way to delete one of these rows and leave the other unless there are other columns that differentiate them:
1--2--3
1--2--3

The only other option is to create a new table by SELECTing these columns and GROUP BY the same columns. Then Drop the original table and rename the new one to the original name.

Be One with the Optimizer
TG

marek
Starting Member

34 Posts

Posted - 2009-12-09 : 13:25:51

I understand I create "as" table and then sort and delete.

...yosiasz
Which ones must remain and Which ones must go is any of these (arbitrary)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-09 : 13:29:08

I meant this:

1.
select a,b,c into newTable from myTable group by a,b,c

2.
drop myTable

3.
sp_rename 'dbo.newTable', 'myTable'

EDIT:
And then put primary keys and/or unique constraints on your tables to prevent duplicates

Be One with the Optimizer
TG

marek
Starting Member

34 Posts

Posted - 2009-12-09 : 13:57:38

Thanks everyone Good night

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-12-09 : 15:41:20

create table #tmp (
c1 int,
c2 int,
c3 int
)

truncate table #tmp
go
set rowcount 0
go

insert into #tmp
select 1,1,2 union all
select 1,1,2 union all
select 1,1,2 union all
select 1,2,2 union all
select 1,2,2 union all
select 1,2,2 union all
select 1,2,3 union all
select 1,2,3 union all
select 1,2,3 union all
select 2,3,2
GO

set rowcount 1
GO

declare @c1 int, @c2 int, @c3 int

while exists (
select c1,c2,c3 from #tmp group by c1,c2,c3 having count(*) != 1
)
BEGIN
select @c1=c1,@c2=c2,@c3=c3 from #tmp group by c1,c2,c3 having count(*) != 1

delete from #tmp
where c1=@c1 and c2=@c2 and c3=@c3
END
GO

set rowcount 0
GO

select * from #tmp order by 1,2,3

drop table #tmp

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-10 : 05:06:43

I don't really understand why my suggestion was ignored, so here it is again with jhocutt's structure/data...

create table #tmp (
c1 int, 
c2 int, 
c3 int
)

truncate table #tmp
go
set rowcount 0
go

insert into #tmp 
select 1,1,2 union all
select 1,1,2 union all
select 1,1,2 union all
select 1,2,2 union all
select 1,2,2 union all
select 1,2,2 union all
select 1,2,3 union all
select 1,2,3 union all
select 1,2,3 union all
select 2,3,2 
GO

--the bit that deletes the duplicates
; with t1 as (select *, row_number() over (partition by c1, c2, c3 order by c1) as row from #tmp)
delete from t1 where row > 1
--/

select * from #tmp order by 1,2,3

drop table #tmp

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-12-10 : 09:03:33

I didn't ignore it, yours is infinitely more elegant, I was just providing one that would also work on 2000 for those of us stuck in the past.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-10 : 09:16:21

quote:
Originally posted by jhocutt

create table #tmp (
c1 int,
c2 int,
c3 int
)

truncate table #tmp
go
set rowcount 0
go

insert into #tmp
select 1,1,2 union all
select 1,1,2 union all
select 1,1,2 union all
select 1,2,2 union all
select 1,2,2 union all
select 1,2,2 union all
select 1,2,3 union all
select 1,2,3 union all
select 1,2,3 union all
select 2,3,2
GO

set rowcount 1
GO

declare @c1 int, @c2 int, @c3 int

while exists (
select c1,c2,c3 from #tmp group by c1,c2,c3 having count(*) != 1
)
BEGIN
select @c1=c1,@c2=c2,@c3=c3 from #tmp group by c1,c2,c3 having count(*) != 1

delete from #tmp
where c1=@c1 and c2=@c2 and c3=@c3
END
GO

set rowcount 0
GO

select * from #tmp order by 1,2,3

drop table #tmp

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Beware that this is highly time consuming query for larger tables

Madhivanan

Failing to plan is Planning to fail

marek
Starting Member

34 Posts

Posted - 2009-12-10 : 16:25:27

quote:
Originally posted by RyanRandall

I don't really understand why my suggestion was ignored, so here it is again with jhocutt's structure/data...

no! no! I not ignored your first solution, On the contrary. I use your suggestion. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources