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'
santaspores1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
0
rgc6789Commented:
Yes, once you have 2 records that are the same, SQL cannot do anything that will then make 1 unique because it can't distinguish one from the other.

Run a delete statement that will delete both of them and then re-insert it back.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
You may copy distinct records into temp table:

SELECT DISTINCT * INTO #TempTable FROM YourTable

TRUNCATE YourTable

INSERT INTO YourTable SELECT * FROM #TempTable

DROP #TempTable
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

pcelbaCommented:
You may execute above code just for your two duplicate records by adding appropriate WHERE clause.
0
santaspores1Author Commented:
easy enough - thanks!
0
wiljeCommented:
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

0
rgc6789Commented:
The other options also work, but for 2 rows I find it easier to just duplicate it manually.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.