Solved

Remove dup record from SQL

Posted on 2009-07-06
7
285 Views
Last Modified: 2012-05-07
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'
0
Comment
Question by:santaspores1
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24786291
0
 
LVL 5

Accepted Solution

by:
rgc6789 earned 500 total points
ID: 24786294
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
 
LVL 41

Expert Comment

by:pcelba
ID: 24786332
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 41

Expert Comment

by:pcelba
ID: 24786341
You may execute above code just for your two duplicate records by adding appropriate WHERE clause.
0
 

Author Closing Comment

by:santaspores1
ID: 31600208
easy enough - thanks!
0
 
LVL 7

Expert Comment

by:wilje
ID: 24786568
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
 
LVL 5

Expert Comment

by:rgc6789
ID: 24786587
The other options also work, but for 2 rows I find it easier to just duplicate it manually.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question