Deleting mutual data sets - 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

Deleting mutual data sets

Author

Topic

saif3r
Starting Member

3 Posts

Posted - 2011-02-07 : 08:34:55

Hello
im using sql server 2005 and id like to ask u, how to delete mutual data sets contained in 2 columns? My db looks like this:

Column 1 Column 2
1 1
1 1
1 2
1 2
1 2
2 1
2 2
2 2
2 5
2 5
2 5

And id like to make it look like this:

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 08:56:32

do you want just to display it as you want or actually want to remove the duplicate records from the table?

saif3r
Starting Member

3 Posts

Posted - 2011-02-07 : 08:58:45

I'd like to remove them from database.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 09:31:20

So how do you identify which to keep & which to delete, or doesn't it matter? What else uniquely identifies the rows?
BTW if having duplicates is a problem you should put a unique constraint on the 2 columns once you've cleaned them up.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 09:36:06

you can do it via two ways 1) through a loop 2) by introducing a replica table of the existing one with an addition of a column identifying multiples by numeric sequence ... once loaded with that sequence remove all rows having rownumber>1

got the idea? or should i write the whole code for you ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-07 : 10:21:40

Refer method 6
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-02-07 : 18:39:33

quote:
Originally posted by saif3r

Hello
im using sql server 2005 and id like to ask u, how to delete mutual data sets contained in 2 columns? My db looks like this:
Column 1 Column 2
1 1
1 1
1 2
1 2
1 2
2 1
2 2
2 2
2 5
2 5
2 5

And id like to make it look like this:
1 1
1 2
2 1
2 2
2 5

declare @t table
(
Column1 int,
Column2 int
)
insert into @t
select 1, 1 union all
select 1, 1 union all
select 1, 2 union all
select 1, 2 union all
select 1, 2 union all
select 2, 1 union all
select 2, 2 union all
select 2, 2 union all
select 2, 5 union all
select 2, 5 union all
select 2, 5

select * from @t
delete t
from
(
select
row_number() over (partition by column1, column2
order by column1, column2) as rownum,
*
from @t)t
where rownum > 1

select * from @t
--------------------
Results
(11 row(s) affected)
Column1 Column2
----------- -----------
1 1
1 1
1 2
1 2
1 2
2 1
2 2
2 2
2 5
2 5
2 5

(11 row(s) affected)

(6 row(s) affected)

Column1 Column2
----------- -----------
1 1
1 2
2 1
2 2
2 5

(5 row(s) affected)

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-07 : 23:02:35

also

create table #temp
(
Column1 int,
Column2 int
)
insert into #temp
select 1, 1 union all
select 1, 1 union all
select 1, 2 union all
select 1, 2 union all
select 1, 2 union all
select 2, 1 union all
select 2, 2 union all
select 2, 2 union all
select 2, 5 union all
select 2, 5 union all
select 2, 5

;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Column1, Column2
Order BY Column1, Column2 ) AS RowNumber, *
FROM #temp tbl
)
DELETE FROM CTE Where RowNumber > 1
GO
SELECT * FROM #temp
GO
DROP TABLE #temp

--Ranjit

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-08 : 05:48:48

And when you are done specify some constraints so you never need to do this again!

If the combination of the columns must be UNIQUE then declare them so!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

saif3r
Starting Member

3 Posts

Posted - 2011-02-10 : 04:40:22

Thanks guys, ill give it a try.
There is one prob, 2 tables i gave in 1st post, are just an example, the real tables contains much more records so i dont think select with union will do the deal in above example.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-02-10 : 05:31:26

The union part was just to create a table with sample data...

I'd do this if those are the only two columns in the table:



SELECT DISTINCT col1,col2 INTO #Temp FROM YourTable

DELETE YourTable

--add a constraint so duplicates aren't allowed again!
ALTER TABLE YourTable
ADD CONSTRAINT Pk_Col1Col2 PRIMARY KEY (Col1,Col2)

INSERT INTO YourTable SELECT * FROM #Temp

DROP #Temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources