I want to set a filed to primary key.
But there duplicate value in it.
How can I find all rows with the duplicate value of that field?
ad
Itzik Ben-Gan written a greate examples about that
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
> I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>
|||Hi
If the whole row is duplicated then you can create a temporary table with
the same structure that is populated with
INSERT INTO #tmp SELECT DISTINCT * FROM MyTable
you can then
TRUNCATE MyTable
and re-insert the value back
INSERT INTO MyTable SELECT * FROM #tmp
If this is not the case, then you will need to differentiate the records
somehow and then choose one to keep e.g. If there say a datetime column
called date_created and you wish to keep the earliest and you primary key is
a column(s) called PK then (assuming date_created is unique for each pk)
DELETE FROM MyTable
FROM MyTable t
WHERE t.date_created > ( SELECT MIN(date_created) FROM MyTable M where m.pk
= t.pk )
John
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>
|||On Mon, 28 Mar 2005 16:19:13 +0800, ad wrote:
>I want to set a filed to primary key.
>But there duplicate value in it.
>How can I find all rows with the duplicate value of that field?
>
Hi ad,
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo left out a little part on his query... on the having clause
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*) > 1
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>
|||On Mon, 28 Mar 2005 08:26:42 -0500, Wayne Snyder wrote:
>Hugo left out a little part on his query... on the having clause
Ouch! Thanks for catching that, Wayne!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
