Eliminating duplicated records from the table

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

Eliminating duplicated records from the table

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-20 : 14:00:57

Hi
In the following table knew that there are some duplicated records and also some records that their kes sets (ODCM) are the same but the value of the V0 is slightly different in each record.

I have found both these two groups of records through the following command:

Command 1: SELECT O,D,C,M,V0 FROM myTable GROUP BY O,D,C,M,V0 HAVING COUNT(*)>1

Comand2: SELECT O,D,C,M FROM myTable GROUP BY O,D,C,M HAVING COUNT(DISTINCT V0)>1

Now I want to remove one of the duplicated records and also from the records with same key set but different V0 value, only keep one of the record and eliminate the other one.

Question:
Remove the duplicated records from command 1,
Keep one of the records of command 2 and remove the other one. (there are records with same key set (ODMC) but different V0, delete all except one)

Thank you

O D C M V0
04000US01 010 2 02 4
04000US01 010 3 02 4
04000US01 010 5 02 4
04000US01 010 2 03 730
04000US01 010 3 03 644
04000US01 010 4 03 402
04000US01 010 5 03 242
04000US01 010 6 03 86
04000US01 010 13 03 15
04000US01 010 15 03 15
04000US01 010 19 03 61
04000US01 010 2 04 712
04000US01 010 3 04 712

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-20 : 14:07:38

To clarify, you want to delete all the rows for #1, or just keep 1 row for each group? And for #2, which row do you want to keep?

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-20 : 15:22:20

For # 1 if when there is duplication let say ODMCV0(1) = ODMCV0(2) = ODMCV0(3), I want to keep only one of the records.
For # 2 if there is duplication in Key set like ODMC(1) = ODMC(2)=ODMC(3) but V0(1)is not equal to V0(2)and V0(3), here I want to keep one of them no matter which one!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-20 : 15:27:39

SELECT O, D, C, M, MAX(V0) V0 INTO #temp FROM myTable GROUP BY O, D, C, M
TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM #temp
DROP TABLE #temp

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-23 : 11:58:23

Hi,
I am doing these queries in a view. and I receive the following error:
(390312 row(s) affected)
Msg 4708, Level 16, State 2, Line 2
Could not truncate object 'dev.testV0' because it is not a table.

1-Should I change the format of view to a table or I can do it for a view as well.
2- for the records with douplicate keys and distinct values (#2) can I make a average of all the values and keep one key with the average value.

Thank you

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-23 : 13:16:03

1. For a view, you would either change the definition to return only one row, or remove the duplicates from the underlying table(s).
2. Sure, you can use any aggregate function instead of MAX().

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-24 : 16:51:13

Thank you. But still I can not make the first query which is
SELECT O, D, C, M, MAX(V0) V0 INTO #temp FROM myTable GROUP BY O, D, C, M
TRUNCATE TABLE myTable

Thx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-24 : 18:03:13

You can't use TRUNCATE TABLE on a view, and you probably can't DELETE from it either, and you probably shouldn't for what you're trying to accomplish. Post the CREATE VIEW statement and the CREATE TABLE statements for the tables it accesses.

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-25 : 14:32:43

Create view [dev].[testV0]
as

SELECT O
,D
,M
,C
,V0

FROM [dev].[GAMS_Init_Var]
where V0 is not null

That is the "view" that I have to create [testV0]. and for this view I want to do the above 2 queries. One to delete one of the duplications. and the second is to average the value of "V0" for the records with the same key sets of (ODCM).

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-25 : 17:35:03

SELECT O, D, C, M, MAX(V0) V0 INTO #temp FROM [dev].[GAMS_Init_Var] GROUP BY O, D, C, M
TRUNCATE TABLE [dev].[GAMS_Init_Var]
INSERT INTO [dev].[GAMS_Init_Var] SELECT * FROM #temp
DROP TABLE #temp

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-27 : 11:30:28

When I try the above mentioned statement:

SELECT O, D, C, M, MAX(V0) V0 INTO #temp FROM [dev].[V0] GROUP BY O, D, C, M
TRUNCATE TABLE [dev].[V0]
INSERT INTO [dev].[V0] SELECT * FROM #temp
DROP TABLE #temp

it gives me the following error:

Msg 4406, Level 16, State 1, Line 3
Update or insert of view or function 'dev.GAMS_Init_Var' failed because it contains a derived or constant field.

and I have to mention that the view V0 is made from dev.GAMS_Init_Var as:
cview [dev].[V0]
as
SELECT O
,D
,M
,C
,V0

FROM [dev].[GAMS_Init_Var]
where V0 is not null

GO

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-31 : 10:06:39

is there any comment!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources