duplicate entries - 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

General SQL Server Forums

New to SQL Server Programming

duplicate entries

Author

Topic

sqlkid
Starting Member

41 Posts

Posted - 2009-06-28 : 13:18:36

I have a table like below :
Name Age Location Pin Bnk Homeno nam+homno amount
1)I need to identify rows which have duplicate nam+homno combination and then delete them retaining only 1 entry – how can this be done ? I have identified the duplicates :
SELECT nam+homno, count(*)
FROM T1
GROUP BY nam+homno
HAVING count(*) > 1
But the no of entries are too many to be delted individually
2)also can I get the ‘amount’ column updated as 0 for duplicate nam+homno – that is the value of amount will remain as is only for 1 nam+homeno while its duplicates will have the value as zero

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 13:26:19


DELETE t
FROM T1 t
LEFT JOIN (SELECT nam+homno, min(pkcol) as first
FROM T1
GROUP BY nam+homno
HAVING count(*) > 1)t1
ON t1.nam+homno=t.nam+homno
AND t1.first=t.pkcol
WHERE t1.first IS NULL

pkcol is the primary key of your table
make sure you first use select instead of delete in above query to check if it returns correctly records to be deleted
2.once you delete the records, no duplicate entry exists, then what's the purpose of updating to 0?

sqlkid
Starting Member

41 Posts

Posted - 2009-06-28 : 13:41:00

[
2.once you delete the records, no duplicate entry exists, then what's the purpose of updating to 0?
[/quote]
this is independent of ques 1 - instead of deleting can i make a value which will be used later as 0 ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 13:42:32

you can:-


UPDATE t
SET t.amount=0
FROM T1 t
LEFT JOIN (SELECT nam+homno, min(pkcol) as first
FROM T1
GROUP BY nam+homno
HAVING count(*) > 1)t1
ON t1.nam+homno=t.nam+homno
AND t1.first=t.pkcol
WHERE t1.first IS NULL

sqlkid
Starting Member

41 Posts

Posted - 2009-06-28 : 13:50:12

srry im litte confused. If t is my table whats T1 & t1?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 13:53:55

quote:
Originally posted by sqlkid

srry im litte confused. If t is my table whats T1 & t1?

ok i've made it clear. T1 is your table. p & q are aliases that i used (shortnames for table as well as derived table query)


DELETE p
FROM T1 p
LEFT JOIN (SELECT nam+homno, min(pkcol) as first
FROM T1
GROUP BY nam+homno
HAVING count(*) > 1)q
ON q.nam+homno=p.nam+homno
AND q.first=p.pkcol
WHERE q.first IS NULL

UPDATE p
SET p.amount=0
FROM T1 p
LEFT JOIN (SELECT nam+homno, min(pkcol) as first
FROM T1
GROUP BY nam+homno
HAVING count(*) > 1)q
ON q.nam+homno=p.nam+homno
AND q.first=p.pkcol
WHERE q.first IS NULL

sqlkid
Starting Member

41 Posts

Posted - 2009-06-29 : 12:13:14

quote:
Originally posted by visakh16

quote:
Originally posted by sqlkid

srry im litte confused. If t is my table whats T1 & t1?

UPDATE p
SET p.amount=0
FROM T1 p
LEFT JOIN (SELECT nam+homno, min(pkcol) as first
FROM T1
GROUP BY nam+homno
HAVING count(*) > 1)q
ON q.nam+homno=p.nam+homno
AND q.first=p.pkcol
WHERE q.first IS NULL

[/code]

The above updates ALL amount columns associated with duplicate hom+name as 0. What I was looking for is to retain one of the original amount value and convert the other into 0. Ie, if there are 3 similar hom+nam, then amount for 2 of them to be made 0 and the other will remain as is. The one to be reatined can be random or if that is not possible, the one with highest age to be retained(eg)

I am not sure whether the delete command also deletes all duplicates

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 12:17:41

it wont update all. it will update all except the first record with same nam+homno value. did you substitute the correct column in place of pkcol. if you're still unclear, post some sample data

sqlkid
Starting Member

41 Posts

Posted - 2009-06-29 : 12:45:08

quote:
Originally posted by visakh16

it wont update all. it will update all except the first record with same nam+homno value. did you substitute the correct column in place of pkcol. if you're still unclear, post some sample data

can you pls check whether im using the query right ?
UPDATE ‘mynewtable’
SET ‘mynewtable’.amount=0
FROM ‘original table’’mynewtable’
LEFT JOIN (SELECT nam+hom, min(amount) as first
FROM ‘original table’
GROUP BY nam+hom
HAVING count(*) > 1)q
ON q.nam+hom=’mynewtable’.nam+hom
AND q.first=’mynewtable’.amount
WHERE q.first IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 13:45:18

what? is amount your primary key of table? i dont think that will be case.

sqlkid
Starting Member

41 Posts

Posted - 2009-07-01 : 12:14:03

quote:
Originally posted by visakh16

what? is amount your primary key of table? i dont think that will be case.

Sorry – I have corrected that. But I think the ‘amount’ column is being updated to zero even for some unique nam+homno entries – I did some random checks. Also the total number of rows in my table = 125000
No of distinct nam+homno = 82000 but number of rows where amount = 0 is 94000. Shoudnt the updation happen only for 125-82 = 43000 cases ?

also does it matter that my pk is a combination of columns - characters&numbers ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:30:40

yup..it will as composite pk just means combination is unique not individual field values.

b/w are you using sql 2005?

sqlkid
Starting Member

41 Posts

Posted - 2009-07-01 : 12:48:58

quote:
Originally posted by visakh16

yup..it will as composite pk just means combination is unique not individual field values.

b/w are you using sql 2005?

sql 2000
so can i insert a pk (1,2,3...) as the all rows are unique

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:57:01

yup. but before that you need to drop current primary key and then add new
the addition statement will be


ALTER TABLE yourTable ADD YourNewColName int IDENTITY(1,1) NOT NULL CONSTRAINT column_pk PRIMARY KEY

sqlkid
Starting Member

41 Posts

Posted - 2009-07-01 : 13:14:48

quote:
Originally posted by visakh16

yup. but before that you need to drop current primary key and then add new
the addition statement will be

ALTER TABLE yourTable ADD YourNewColName int IDENTITY(1,1) NOT NULL CONSTRAINT column_pk PRIMARY KEY

Im still getting '0' amount for unique nam+homno . Can you pls chk the below syntax

Update ‘newtable’
set ‘newtable’.amount=0
from ‘originaltable’ ‘newtable’
left join (select nam+homno, min(pkcolumn) as first
from originaltable
group by nam+homno
having count(*)>1)q
on q.nam+homno=’newtable’.nam+homno
and q.first=’newtable’.pkcol
where q.first is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 13:19:53

did you create the pk col and checked if it got the unique values?

sqlkid
Starting Member

41 Posts

Posted - 2009-07-01 : 13:22:33

quote:
Originally posted by visakh16

did you create the pk col and checked if it got the unique values?

added 1,2,3,... as pk column.

''checked if it got the unique values?'' - ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 13:24:54

added 1,2,3,... as pk column?? what does that mean? i told you to create a new pk column

sqlkid
Starting Member

41 Posts

Posted - 2009-07-01 : 13:30:56

quote:
Originally posted by visakh16

added 1,2,3,... as pk column?? what does that mean? i told you to create a new pk column

i deleted my earlier my pk column as it was a combination of columns
then ran the command
ALTER TABLE yourTable ADD YourNewColName int IDENTITY(1,1) NOT NULL CONSTRAINT column_pk PRIMARY KEY

ie, isnt the 1,2,3 column generated my new pk column ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 13:42:58

yup. it is. check it yourself

SELECT newcolumn FROM YourTable ORDER BY newcol

if it came right, then try running earlier query

sqlkid
Starting Member

41 Posts

Posted - 2009-07-01 : 13:54:55

quote:
Originally posted by visakh16

yup. it is. check it yourself

SELECT newcolumn FROM YourTable ORDER BY newcol

if it came right, then try running earlier query

I ran the update query with the new pk col but still returing '0' for many non-duplicate nam+hom entries. From the o/p if i select the nam+hom value (say x) where amount = 0 and run select* from T where nam+home =x, it returns only that row with amount = 0

In the syntax, do i need to define the new table (of update 'newtable') before hand ?
any other chks i shd do ?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources