Duplicate records - with update

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)

Duplicate records - with update

Author

Topic

insight974
Starting Member

2 Posts

Posted - 2007-03-19 : 16:06:46

Hi. I have a table like this:
RecordId, Description, CaseId, Comments(text field)
1 Test1 13 NULL
2 Test1 13 Testing comments
3 Test2 14 Hello, test
4 Test2 14 NULL
5 Test2 14 NULL

I'm trying to remove duplicates (Description+CaseId have to be unique). I would like to remove all except the record having NON NULL comments but I don't have a clue on how to do it. Please help.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 16:28:02

delete t1 from table1 as t1 where t1.recordid not in (
select max(t2.recordid) from table1 as t2 where t2.comments is not null group by t2.description, t2.caseid
)

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-03-19 : 16:34:08

or may be something like this ..



Delete t1 From YourTable t1 Where
Exists 
(
	Select * From YourTable t2 where t1.Description= t2.Descriptions and t1.CaseID = t2.CaseID 
)
and t1.Comment is null

Chirag

http://chirikworld.blogspot.com/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-19 : 16:47:18

also, it's best to make a backup before running such scripts.

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 16:59:50

quote:
Originally posted by chiragkhabaria

or may be something like this ..
Delete t1 From YourTable t1 Where
Exists
(
Select * From YourTable t2 where t1.Description= t2.Descriptions and t1.CaseID = t2.CaseID
)
and t1.Comment is null

Isn't this exactly the same thing as

Delete From YourTable Where Comment is null

???

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-03-20 : 02:54:37

quote:
Originally posted by Peso

quote:
Originally posted by chiragkhabaria

or may be something like this ..
Delete t1 From YourTable t1 Where
Exists
(
Select * From YourTable t2 where t1.Description= t2.Descriptions and t1.CaseID = t2.CaseID
)
and t1.Comment is null
Isn't this exactly the same thing as

Delete From YourTable Where Comment is null

???

Peter Larsson
Helsingborg, Sweden

Oh yes.. Actually i intended to type like this..


Delete t1 From YourTable t1 Where
Exists 
(
	Select * From YourTable t2 where t1.Description= t2.Descriptions and t1.CaseID = t2.CaseID and t1.Comment is Not null
)
and t1.Comment is null

But i think this will not work for the records which are having the duplicate values with none of the comments is not null..

Well i guess your solution should work..

Chirag

http://chirikworld.blogspot.com/

insight974
Starting Member

2 Posts

Posted - 2007-03-22 : 16:08:22

Thank you all. The solutions did work very well. Sorry I didn't answer before but I was really sick. Anyway, your help is really appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources