Deleting partially duplicated data

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)

Deleting partially duplicated data

Author

Topic

kinny_k
Starting Member

5 Posts

Posted - 2010-11-03 : 11:45:33

I have a student_test table where some students have two rows identical with the exception of ONE field (one row the field is populated and the next, it's NULL). How do I approach deleting just the row where the field in question is NULL?

I've successful dealt with situation where the entire row was duplicated, just not a case where a single field was different.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 11:51:05

Give DDL, example data and we will give you a solution using row_number().

No, you're never too old to Yak'n'Roll if you're too young to die.

kinny_k
Starting Member

5 Posts

Posted - 2010-11-03 : 12:46:53

Table

USE [SERF]
GO

/****** Object:  Table [dbo].[student_test]    Script Date: 11/03/2010 09:55:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[student_test](
	[student_test_id] [int] NOT NULL,
	[test_prog_id] [int] NOT NULL,
	[student_id] [decimal](10, 0) NOT NULL,
	[district_id] [decimal](9, 0) NOT NULL,
	[school_id] [decimal](9, 0) NOT NULL,
	[entry_date] [datetime] NOT NULL,
	[school_number] [nvarchar](3) NOT NULL,
	[school_year] [nvarchar](4) NULL,
	[test_participation] [nvarchar](2) NULL,
	[test_non_participation] [nvarchar](2) NULL,
	[test_overall_score] [decimal](4, 1) NULL,
	[proficiency] [int] NULL,
	[scaled_score] [int] NULL,
	[test_medium] [nvarchar](2) NULL,
	[warehouse_entry_date] [datetime] NULL,
	[validated_proficiency] [int] NULL,
	[validated_test_overall_score] [decimal](4, 1) NULL,
	[include_in_ayp] [nvarchar](1) NULL,
	[include_in_upass_summary] [nvarchar](1) NULL,
	[include_in_profile_summary] [nvarchar](1) NULL,
	[filename] [nvarchar](50) NULL,
	[line_number] [decimal](10, 0) NULL,
	[test_year] [nvarchar](4) NULL,
	[upass_proficiency] [nvarchar](2) NULL,
	[value_table_score] [decimal](4, 1) NULL,
	[incl_in_upass_progress] [nvarchar](1) NULL,
	[include_in_upass] [nvarchar](1) NULL,
	[first_time_test_taken] [nvarchar](1) NULL,
	[include_in_ayp_proficiency] [nvarchar](1) NULL,
	[include_in_ayp_participation] [nvarchar](1) NULL,
	[teacher_id] [decimal](9, 0) NULL,
	[teacher_id_match_ind] [bit] NULL,
	[teacher_id_section_9_2] [nvarchar](11) NULL
) ON [PRIMARY]

GO

Sample Data:

INSERT INTO student_test
(      student_test_id,
      test_prog_id,
      student_id,
      district_id,
      school_id,
      entry_date,
      school_number,
      school_year,
      test_participation,
      test_non_participation,
      test_overall_score,
      proficiency,
      scaled_score,
      test_medium,
      warehouse_entry_date,
      validated_proficiency,
      validated_test_overall_score,
      include_in_ayp,
      include_in_upass_summary,
      include_in_profile_summary,
      filename,
      line_number,
      test_year,
      upass_proficiency,
      value_table_score,
      incl_in_upass_progress,
      include_in_upass,
      first_time_test_taken,
      include_in_ayp_proficiency,
      include_in_ayp_participation,
      teacher_id,
      teacher_id_match_ind,
      teacher_id_section_9_2)
SELECT '11687138','5610','1532528','1002','1023','Aug 15 2007 12:00AM','404','2008','','','84.0','4','170','PS','Nov 10 2008 12:00AM','4','84.0','N','Y','Y','ALL_SY2008_2008ES0912_WHOUSE.TXT','12346','2008','4','225.0','Y','Y','Y','N','N','99177','1','99177', UNION ALL
SELECT '12014763','5620','1312645','1002','1028','Aug 15 2007 12:00AM','425','2008','','','70.0','3','165','O','Nov 10 2008 12:00AM','3','70.0','Y','Y','Y','ALL_SY2008_2008LA0808_WHOUSE.TXT','30076','2008','3','200.0','Y','Y','Y','B','B','408008','0','408008', UNION ALL
SELECT '13097829','5715','1313367','1002','1033','Aug 14 2008 12:00AM','712','2009','1','','22.0','1','139','O','Jul 27 2009 12:00AM','1','22.0','Y','Y','Y','ALL_SY2009_2009LA0808_WHOUSE.TXT','32739','2009','1a','0.0','Y','Y','Y','D','B','79113','1','79113', UNION ALL
SELECT '13173103','5717','1319644','1002','1037','Aug 12 2008 12:00AM','725','2009','','','86.0','4','173','O','Jul 27 2009 12:00AM','4','86.0','Y','Y','Y','ALL_SY2009_2009LA1010_WHOUSE.TXT','13634','2009','4','225.0','Y','Y','Y','B','B','75719','1','75719', UNION ALL
SELECT '13310386','5711','1944282','1002','1024','Aug 12 2008 12:00AM','408','2009','','','64.0','3','162','PS','Jul 27 2009 12:00AM','3','64.0','Y','Y','Y','ALL_SY2009_2009AG1712_WHOUSE.TXT','4273','2009','3','125.0','Y','Y','Y','B','B','498230','0','498230', UNION ALL
SELECT '13428110','5684','1313329','1002','1033','Aug 14 2008 12:00AM','712','2009','','','77.0','4','167','O','Jul 27 2009 12:00AM','4','77.0','N','Y','Y','ALL_SY2009_2009ES0912_WHOUSE.TXT','6113','2009','4','300.0','Y','Y','Y','N','N','437357','1','437357', UNION ALL
SELECT '13594164','5705','2204560','1002','1019','Aug 12 2008 12:00AM','130','2009','','','86.0','4','170','PS','Jul 27 2009 12:00AM','4','86.0','Y','Y','Y','ALL_SY2009_2009LA0404_WHOUSE.TXT','23494','2009','4','225.0','Y','Y','Y','B','B','92503','1','92503', UNION ALL
SELECT '13636614','5706','1943962','1002','1009','Aug 12 2008 12:00AM','110','2009','','','91.0','4','173','O','Jul 27 2009 12:00AM','4','91.0','Y','Y','Y','ALL_SY2009_2009LA0505_WHOUSE.TXT','2627','2009','4','225.0','Y','Y','Y','B','B','98032','1','98032', UNION ALL
SELECT '13964239','5680','2484130','1002','1014','Aug 12 2008 12:00AM','120','2009','1','','61.0','2','157','O','Jul 28 2009 12:00AM','2','61.0','N','Y','Y','ALL_SY2009_2009SC0505_WHOUSE.TXT','29314','2009','2b','350.0','Y','Y','Y','N','N','544457','0','544457', UNION ALL
SELECT '14042092','5682','1313694','1002','186048','Aug 12 2008 12:00AM','303','2009','','','90.0','4','176','O','Jul 28 2009 12:00AM','4','90.0','N','Y','Y','ALL_SY2009_2009SC0707_WHOUSE.TXT','21679','2009','4','225.0','Y','Y','Y','N','N','553838','1','553838', UNION ALL
SELECT '14079092','5683','2077555','1002','1028','Aug 12 2008 12:00AM','425','2009','','','96.0','4','187','O','Jul 28 2009 12:00AM','4','96.0','N','Y','Y','ALL_SY2009_2009SC0808_WHOUSE.TXT','28824','2009','4','225.0','Y','Y','Y','N','N','538706','1','538706'

Hope I did this correctly.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 13:29:43

I can't see any duplicates in your sample data.
Which column will be NULL if a duplicate exists?

No, you're never too old to Yak'n'Roll if you're too young to die.

kinny_k
Starting Member

5 Posts

Posted - 2010-11-03 : 13:31:36

quote:
Originally posted by webfred

I can't see any duplicates in your sample data.
Which column will be NULL if a duplicate exists?

No, you're never too old to Yak'n'Roll if you're too young to die.

teacher_id_section_9_2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 13:39:17

Be sure to do this in a test environment:
delete dt
from
(
select row_number() over(partition by student_id,teacher_id_section_9_2 order by teacher_id_section_9_2 DESC) as rownum,
*
from student_test
)dt
where rownum > 1

No, you're never too old to Yak'n'Roll if you're too young to die.

kinny_k
Starting Member

5 Posts

Posted - 2010-11-03 : 15:24:51

I rewrote your suggestion a bit and it worked:


delete dt
from
(
select row_number() over(partition by student_id 
      ,[test_prog_id]
      ,[district_id]
      ,[school_id]
      ,[entry_date]
      ,[school_number]
      ,[school_year]
      ,[test_language]
      ,[test_participation]
      ,[test_non_participation]
      ,[test_overall_score]
      ,[proficiency]
      ,[scaled_score]
      ,[test_medium]
      ,[warehouse_entry_date]
      ,[validated_proficiency]
      ,[validated_test_overall_score]
      ,[include_in_ayp]
      ,[include_in_upass_summary]
      ,[include_in_profile_summary]
      ,[filename]
      ,[line_number]
      ,[class_identifier]
      ,[test_year]
      ,[upass_proficiency]
      ,[value_table_score]
      ,[incl_in_upass_progress]
      ,[include_in_upass]
      ,[first_time_test_taken]
      ,[prev_yr_test_flag]
      ,[include_in_ayp_proficiency]
      ,[include_in_ayp_participation]
      ,[teacher_id]
      ,[teacher_id_match_ind]
      ,[section_number] order by teacher_id_section_9_2 DESC) as rownum,
*
from student_test
)dt
where rownum > 1

THANKS!!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 15:30:03

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources