November 14, 2007 at 3:45 am
How to delete the duplicate value in the table..
November 14, 2007 at 3:54 am
I assume that this table does not have a primary key 🙂 (it is bad, but it is of course another story)
You could build a new table with only the unique values (select distinct * from oldtable), and then replace the old table. (for replacing you may want to look at sp_rename.
Regards,
Andras
November 14, 2007 at 3:58 am
Hi
Please tell me how to delete when no primary key..
Regards..
karthikeyan MCAD
November 14, 2007 at 4:01 am
If there is a duplicate row, and you only want to delete one of them (or only a certain number if there are more), then you can do something ugly like:
set rowcount 1
GO
delete from ..... where col1=value1 AND col2=vlaue2 AND ...
GO
set rowcount 0
GO
the rowcount 1 will ensure that only one row is deleted, even if there are many rows matching the where clause.
Regards,
Andras
November 14, 2007 at 9:42 am
karthikeyan (11/14/2007)
HiPlease tell me how to delete when no primary key..
Regards..
karthikeyan MCAD
Karthikeyan
This is probably not the mose elegant way to do this, but it works for a job I run every night to delete dupes where all fields are the same other than the identity column, that is why I suggested you add an identity column. I have a flaw in my front end app that allows for dupes to be entered, and I have not had time to deal with yet. I would *strongly* suggest you dump your production table into a temp table to test. You will have to add all the columns you want to look at in the where clauses.
--First create a test table
IF OBJECT_ID('TempDB..#Test','u') IS NOT NULL
DROP TABLE #Test
CREATE TABLE #Test
(
Col1 VARCHAR(20),
Col2 VARCHAR(20),
Col3 VARCHAR(20),
Col4 VARCHAR(20),
Col5 VARCHAR(20)
)
GO
--Then insert the test date, with lines 6 and 7 being dupes
INSERT INTO #Test
SELECT 'Blue','is','the','first','pair' UNION ALL
SELECT 'Orange','is','the','second','pair' UNION ALL
SELECT 'Green','is','the','third','pair' UNION ALL
SELECT 'Brown','is','the','fourth','pair' UNION ALL
SELECT 'Slate','is','the','fifth','pair' UNION ALL
SELECT 'Orange','is','the','second','pair' UNION ALL
SELECT 'Brown','is','the','fourth','pair'
GO
--Add an identity column to the table for use in deleting the dupes
ALTER TABLE #test
ADD ID INT IDENTITY(1,1)
GO
--Check #test first
SELECT *
FROM #test
--Delete the dupes
DELETE t1
FROM #test t1,
(--Select the MIN ID of the dupe records. It only stands to reason
--these were in the table first
SELECT
MIN(t1.id)ID ,t1.col1,t1.col2,t1.col3,t1.col4,t1.col5
FROM
(--Derived table t1 selects all the dupes, based on the columns you specify
--you would need to add any columns you want to look at as being criteria
--for dupe records
SELECT
t1.*
FROM #test t1,
#test t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND t1.col3 = t2.col3
AND t1.col4 = t2.col4
AND t1.col5 = t2.col5
AND t1.id <> t2.id
) t1,
#test t2
WHERE t1.col1 = t2.col1
AND t1.id <> t2.id
GROUP BY t1.col1,t1.col2,t1.col3,t1.col4,t1.col5
) t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND t1.col3 = t2.col3
AND t1.col4 = t2.col4
AND t1.col5 = t2.col5
AND t1.id <> t2.id
--Check #test after deletion
SELECT *
FROM #test
Hope this helps
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 14, 2007 at 7:32 pm
If there is no primary key, another way is to SELECT DISTINCT into a temporary table, delete the original table and then INSERT the values back into the original table.
K. Brian Kelley
@kbriankelley
November 14, 2007 at 9:30 pm
When I tried an approach (just messing around) similar to the
col1=val1 and col2=val2....
when one of the cols had nulls then it doesn't equate to another null so the row remains. Unless you want to get more complex and ugly by adding double checks for each col one for equality and another for IS NULL.
I think using set-related approach is much better.
November 16, 2007 at 7:09 am
toniupstny (11/14/2007)
When I tried an approach (just messing around) similar to thecol1=val1 and col2=val2....
when one of the cols had nulls then it doesn't equate to another null so the row remains. Unless you want to get more complex and ugly by adding double checks for each col one for equality and another for IS NULL.
I think using set-related approach is much better.
You can use COALESCE(col1, '') = COALESCE(val1, '') and ...
November 16, 2007 at 1:59 pm
Oh... and if you want to keep the same identity numbers SET IDENTITY_INSERT tablename ON before doing the copy out and recopy then SET IDENTITY_INSERT ... OFF when all done.
January 27, 2010 at 5:40 pm
-- Two methods I really like (and plagiarized from people on here).
create table #ttemp
(cola int not null)
insert into #ttemp (cola) values (1)
insert into #ttemp (cola) values (1)
insert into #ttemp (cola) values (1)
insert into #ttemp (cola) values (2)
insert into #ttemp (cola) values (2)
insert into #ttemp (cola) values (2)
insert into #ttemp (cola) values (2)
insert into #ttemp (cola) values (2)
insert into #ttemp (cola) values (3)
insert into #ttemp (cola) values (4)
insert into #ttemp (cola) values (4)
select * from #ttemp
-- method 1
delete a
from #ttemp a
where %%LockRes%% <> (SELECT Min(%%LockRes%%)
FROM #ttemp t1
where t1.cola = a.cola)
-- method 2
--; WITH #ttempCTE
--AS
--(
-- SELECT ROW_NUMBER() OVER( PARTITION BY cola ORDER BY cola ) AS RowNumber
-- FROM #ttemp
--)
--DELETE
--FROM #ttempCTE
--WHERE RowNumber != 1
select * from #ttemp
drop table #ttemp
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply