Deleting duplicate data

Hello there
I've imported table without any unique key
The table have some duplicatate data on it. and i need to delete the
duplicate data
So far i had to alter the table and add unique field, and use it to delete
the duplicate records
Is there a way to do this without altering the table?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilRoy Goldhammer wrote:
> Hello there
> I've imported table without any unique key
> The table have some duplicatate data on it. and i need to delete the
> duplicate data
> So far i had to alter the table and add unique field, and use it to delete
> the duplicate records
> Is there a way to do this without altering the table?
> --
> =F8=E5=F2=E9 =E2=E5=EC=E3=E4=EE=F8
> =F2=FA=E9=E3 =E4=F0=E3=F1=FA =FA=E5=EB=F0=E4
> =E8=EC' 03-5611606
> =F4=EC=E0' 050-7709399
> =E0=E9=EE=E9=E9=EC: roy@.atidsm.co.il
SELECT ...
INTO tmp
FROM your_table
GROUP BY keycol1, keycol2, keycol3
HAVING COUNT(*) > 1 ;
DELETE FROM your_table
WHERE EXISTS
(SELECT *
FROM tmp AS T
WHERE T.keycol1 =3D your_table.keycol1
AND T.keycol2 =3D your_tabkle.keycol2
AND T.keycol3 =3D your_tabkle.keycol3) ;
INSERT INTO yourtable (...)
SELECT ...
FROM tmp ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Roy
Please take a look at these examples written by Itzik Ben-Gan
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OSv$5ruKGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hello there
> I've imported table without any unique key
> The table have some duplicatate data on it. and i need to delete the
> duplicate data
> So far i had to alter the table and add unique field, and use it to delete
> the duplicate records
> Is there a way to do this without altering the table?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||if number of rows are less than you can create temp table copy distinct
row in it, truncate main table and insert rows from temp table to main
table.
select * into temptable from (select distinct * from t1 ) a
truncate table t1
insert into t1 select * from temptable
drop table temptable.
else create identity column in the table and use it to delete data.
Regards
Amish Shahsql