asked on

Remove dup record from SQL

I have an sql server 2005 database table.
the first column is a uniqueidentifier but there is no primary key.
I did a copy paste on a row thinking it would paste everything ACCEPT the uniqueidentifier coumn.
However, it copied everything... so now I have an exact dup record.

I cannot delete the dup.
I get the "The row value(s) updated or deleted either do not make the row unique..." msg

I tried to add a field and give it a separate value for the two records... but it will not let me edit the record to add data to the new column (same msg as above).

The following causes sql to crash
set rowcount 1
delete from mytable
where mycolumn = 'myvalue'

Aneesh

http://sequelserver.blogspot.com/2008/02/deleting-duplicate-records.html

ASKER CERTIFIED SOLUTION

rgc6789

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

You may copy distinct records into temp table:

SELECT DISTINCT * INTO #TempTable FROM YourTable

TRUNCATE YourTable

INSERT INTO YourTable SELECT * FROM #TempTable

DROP #TempTable

Pavel Celba

You may execute above code just for your two duplicate records by adding appropriate WHERE clause.

santaspores1

ASKER

easy enough - thanks!

wilje

You can use a CTE to identify your duplicate rows and delete them.

Use tempdb;
Go
 
If object_id('tempdb..#temp', 'U') Is Not Null
   Begin;
        Drop Table dbo.#temp;
   End;
   
Create Table #temp (id uniqueidentifier, data varchar(max));
 
Declare @myId uniqueidentifier;
Set @myId = newid();
 
Select @myId;
 
Insert Into #temp (id, data) Values(@myId, 'mydata');
Insert Into #temp (id, data) Values(@myId, 'mydata');
 
Select * From #temp;
 
;With cteDUPS (id, rn)
As (Select id
           ,row_number() over(partition By id Order By id) rn
      From #temp
)
Delete 
  From cteDUPS
 Where rn > 1;
 
Select * From #temp;

Open in new window

rgc6789

The other options also work, but for 2 rows I find it easier to just duplicate it manually.