Update/Delete duplicate rows 1

[yorge]

Hi Guys,

Given the rows on PETOWNER table

ID PETID OWNER
===================================
1 1 Joe
2 2 Jane
3 3 Joey
4 4 Roy
5 3 Rey
6 6 James
7 5 Jet
===================================



Given the rows on PET table

ID ANIMALGROUPID TYPE TAGID
==========================================================
1 1 Cat 1
2 1 Cat. 2
3 1 Cat 3
4 2 Dog 10
5 3 Bird. 12
6 3 Bird 13
===========================================================


I would like to update the PETOWNER.PETID so that the final result will be:

ID PETID OWNER
===================================
1 1 Joe
2 1 Jane
3 1 Joey
4 4 Roy
5 1 Rey
6 6 James
7 6 Jet
===================================



Then delete all rows on PET table with duplicate TYPE (including those with dot (".") at the end of the type)
so that the result will be:
Note that the "Cat" type has 3 rows (1 with dot and 2 without). In this case, the 1st "Cat" without the dot will be retain.


ID ANIMALGROUPID TYPE TAGID
=======================================================
1 1 Cat 1
4 2 Dog 10
6 3 Bird 13
=======================================================



Thanks in advance,
Yorge

 

[johnherman]

something like this.....(untested)

CREATE TABLE NEW_PET_TABLE AS (
select ID,ANIMALGROUPID,TYPE,TAGID from (
select min(P1.ID) from PET P1 where P1.TYPE in (
select distinct(P2.TYPE) from PET P2)));

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[yorge]

Hi johnherman,

It didn't work. Also aside from removing the duplicates on PET table, there's also a need to UPDATE the PETOWNER.PETID as specified above.
Anybody can please help?

Yorge

 

[yorge]

Hi Guys,

Using CTE,

WITH CTE
AS
(
SELECT ID,ANIMALGROUPID,TYPE,TAGID,
ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY TYPE) AS DuplicateCount
FROM PET
)

DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

I can get this result:

ID ANIMALGROUPID TYPE TAGID
=======================================================
1 1 Cat 1
2 1 Cat. 2
4 2 Dog 10
5 3 Bird. 12
6 3 Bird 13
=======================================================

Anybody can suggest or modify my query above to remove PET.ID = 2 and 5? The ones where TYPE have dot "." value at the end
to make the final result as:



ID ANIMALGROUPID TYPE TAGID
=======================================================
1 1 Cat 1
4 2 Dog 10
6 3 Bird 13
=======================================================

Thanks in advance,
Yorge

 

[johnherman]

You can use the INSTRING command to determine whether the TYPE string has a period or not, and then use those results to filter the dataset.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[gmmastros]

I found a potential solution to your problem. In the code I show below, I put your sample data in to table variables so that I could work on the queries and actually have data to play with.

I encourage you to study the code below to the point where you understand completely what it is doing. Once you are satisified, you can remove the table variables and replace with your actual table names instead.

** Note: It's important that you update the pet owner table before you delete the duplicate rows from the pet table. The challenging part of this code is to update the pet owner table, swapping out bad id's with good id's.

Declare @PetOwner Table (ID Int, PetId Int, Owner VarChar(20))

Insert Into @PetOwner Values(1,1,'Joe')
Insert Into @PetOwner Values(2,2,'Jane')
Insert Into @PetOwner Values(3,3,'Joey')
Insert Into @PetOwner Values(4,4,'Roy')
Insert Into @PetOwner Values(5,3,'Rey')
Insert Into @PetOwner Values(6,6,'James')
Insert Into @PetOwner Values(7,5,'Jet')

Declare @Pet Table(ID int, ANIMALGROUPID int, TYPE VarChar(20), TAGID Int)

Insert Into @Pet Values(1,1,'Cat',1)
Insert Into @Pet Values(2,1,'Cat.',2)
Insert Into @Pet Values(3,1,'Cat',3)
Insert Into @Pet Values(4,2,'Dog',10)
Insert Into @Pet Values(5,3,'Bird.',12)
Insert Into @Pet Values(6,3,'Bird',13)

;With Data As
(
  Select  *, 
          Row_Number() Over (Partition By AnimalGroupId Order By CharIndex('.', Type)) As RowId
  From   @Pet
), UpdateData As
(
  Select A.Id As DeletePetId,
         B.Id As KeepPetId
  From   Data As A
         Inner Join Data As B
           On A.AnimalGroupId = B.AnimalGroupId
           And A.RowId > 1
           And B.RowId = 1
)
Update @PetOwner
Set    PetId = UpdateData.KeepPetId
From   @PetOwner As PetOwner
       Inner Join UpdateData
         On PetOwner.PetId = UpdateData.DeletePetId

Select * From @PetOwner

; With Data As
(
  Select  *, 
          Row_Number() Over (Partition By AnimalGroupId Order By CharIndex('.', Type)) As RowId
  From    @Pet
)
Delete
From    Data
Where   RowId > 1

Select * From @Pet

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnherman]

gmmastros is correct. Sorry about my bad syntax with INSTRING. I speak several variants of SQL and CHARINDEX is the correct T-SQL syntax. I really respect people who speak Spanish and Portuguese and don't intermingle the words, since so many words and word-roots are identical.

Oracle Example:
-- Find position of word York
SELECT INSTR('New York', 'York', 1) FROM dual;
-- Result: 5

SQL Server Example:
-- Find position of word York
SELECT CHARINDEX( 'York', 'New York', 1);
-- Result: 5


==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[yorge]

thank you so much gmmastros and johnherman