Removing redundant data - 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 2000 Forums

Transact-SQL (2000)

Removing redundant data

Author

Topic

madhulatha_b
Starting Member

22 Posts

Posted - 2005-02-03 : 02:31:04

I want query for the following problem

suppose that i have a table which has got four columns.
it has got 10 records whose values are same for all the columns.
i mean all 10 records identical.I want to delete the 9 records and keep only one record.

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2005-02-03 : 03:37:04

create table tmp (a int,b int,c int, d int)
insert into tmp select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4
-----------------Once the records are inserted try this option
set rowcount 9
delete tmp
select * from tmp

Enjoy working

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-03 : 04:22:59

CREATE TABLE #MyTemp (Col1 int,Col2 int,Col3 int, Col4 int)

INSERT #MyTemp(Col1,Col2,Col3,Col4)
SELECT DISTINCT Col1,Col2,Col3,Col4 --Instead of distinct GROUP BY
FROM RealTable

TRUNCATE TABLE RealTable

INSERT RealTable(Col1,Col2,Col3,Col4)
SELECT Col1,Col2,Col3,Col4
FROM #MyTemp

Andy

madhulatha_b
Starting Member

22 Posts

Posted - 2005-02-03 : 05:21:40

Is this only soln for this problem. Can I have any other soln.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-03 : 05:28:11

No it isnt the only solution
You have got 2, which both do as you asked, what exactly are you trying to do?

Andy

raja
Starting Member

18 Posts

Posted - 2005-02-03 : 07:25:24

That would be simple

SET ROWCOUNT = 9
GO
DELETE FROM <TABLENNAME>
GO
SET ROWCOUNT = 0
GO

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-03 : 07:50:38

raja thats exactly what vganesh76 said except your syntax is incorrect

quote:

SET ROWCOUNT = 9

SET ROWCOUNT 9

Given the example is hypothetical that is why i asked madhulatha_b what he/she is trying to do
If they have actually got a table with 100,000 rows of 1,2,3,4 and another 100,000 rows 5,6,7,8 then your solution doesnt identify which is redundant/duplicated data and as such deletes all records until the rowcount is reached.
But i suppose the original question has been answered

Andy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-03 : 09:05:12

quote:
Originally posted by vganesh76

create table tmp (a int,b int,c int, d int)
insert into tmp select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4 union all
select 1,2,3,4
-----------------Once the records are inserted try this option
set rowcount 9
delete tmp
select * from tmp

Enjoy working

and add this as last line

set rowcount 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-03 : 09:07:15

and if all columns are repeating then

Select distinct * from table

Madhivanan

BammBamm
Starting Member

9 Posts

Posted - 2005-02-03 : 16:28:31

The more methodical approach would be to:

(1) Create a new "temp" table with the same structure
(2) Populate the "temp" table with the query results of the source table issued with a "Group by" on every column
(3) "Truncate" the source table's contents
(4) Populate the source table with the contents of the temp table
(5) "Drop" the "temp" table

There's a few variations on approach, and you could combine a few steps or change an presumption in the process, but this should be the frame of reference. Be mindful if deviating from this approach and any inherent risk it may introduce, for example:

(Option 1) Populate a new table using Insert Into, drop the original, rename the new.
This is perfectly fine, but you will lose table properties/indices in the process

(Option 2) Table is a production table oprerated on throughout the day.
Wait for production activities to stop and/or issue a downtime before embarking on this as user will certainly be affected when the table is truncated.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-03 : 22:44:37

quote:
Originally posted by BammBamm

The more methodical approach would be to:

(1) Create a new "temp" table with the same structure
(2) Populate the "temp" table with the query results of the source table issued with a "Group by" on every column
(3) "Truncate" the source table's contents
(4) Populate the source table with the contents of the temp table
(5) "Drop" the "temp" table

quote:
Originally posted by AndyB13

CREATE TABLE #MyTemp (Col1 int,Col2 int,Col3 int, Col4 int)

INSERT #MyTemp(Col1,Col2,Col3,Col4)
SELECT DISTINCT Col1,Col2,Col3,Col4 --Instead of distinct GROUP BY
FROM RealTable

TRUNCATE TABLE RealTable

INSERT RealTable(Col1,Col2,Col3,Col4)
SELECT Col1,Col2,Col3,Col4
FROM #MyTemp

Andy

DEJAVU!

Edit: Added my original post

raja
Starting Member

18 Posts

Posted - 2005-02-06 : 01:34:44

sorry !
i didn't check the previous post

raja
Starting Member

18 Posts

Posted - 2005-02-06 : 01:37:46

you're correct AndyB13

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-27 : 07:44:10