Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Deleting duplicate entries based on three columns

Posted on 2005-05-12
5
Medium Priority
?
279 Views
Last Modified: 2010-03-19
Hi again,

I have a table, where entries (according to the source) are supposed to be uniquely identified by the columns Distrikt, Kund and ProdNr.

The script below is modified (taken from a post from scottpletcher). I wonder, does this script remove entries that have the same value on those three columns? When I run it, it seems to delete way too many entries (original table had 1271369 records (that are supposed to be somewhat OK) and only 21837 entries remain after this script has run. ProdNr is actually the ordernumber, but my client says it's not unique unless combined with district and customer (Distrikt, Kund).

Best regards,

henrik


DECLARE dupsCsr CURSOR READ_ONLY FOR
      SELECT Distrikt, Kund,ProdNr, COUNT(*) AS numDups FROM tbakOrder
      GROUP BY Distrikt, Kund,ProdNr HAVING COUNT(*) > 1  -- Change if needed

DECLARE @Distrikt int
DECLARE @Kund int
DECLARE @ProdNr int
DECLARE @numDups INT

OPEN dupsCsr
FETCH NEXT FROM dupsCsr INTO @Distrikt, @Kund, @ProdNr, @numDups
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @numDups = @numDups - 1 --Chg if needed: delete all but [1] of the duplicates
     SET ROWCOUNT @numDups
     DELETE FROM tbakOrder
     WHERE Distrikt = @Distrikt AND (Kund = @Kund AND ProdNr = @ProdNr)
     FETCH NEXT FROM dupsCsr INTO @Distrikt, @Kund, @ProdNr, @numDups
END --WHILE
CLOSE dupsCsr
DEALLOCATE dupsCsr

SET ROWCOUNT 0 --restore default
0
Comment
Question by:henrikatwork
5 Comments
 
LVL 4

Assisted Solution

by:stevetheski
stevetheski earned 800 total points
ID: 13993602
looks like it would work
however i would have done the query much differently

to see how many rows should be deleted run this query

select sum(cnt)
from
(select  Distrikt, Kund, ProdNr, count(*)-1 as cnt
from tbakOrder
group by Distrikt, Kund, ProdNr
having count(*)>1) as x

0
 
LVL 9

Accepted Solution

by:
auke_t earned 800 total points
ID: 13993618
--hi, this script calculates the number of records that remain. Run it on the original table. If it's more than 21837 then your script deletes too many records.

select
   count(*)
from
   tbakOrder
group by
    Distrikt,
    Kund,
    ProdNr
0
 
LVL 4

Expert Comment

by:stevetheski
ID: 13993677
damn i must be tired auke_t
when reading about the deleted records i didnt even think about the good records :)
my solution is more creative though :)

Steve
0
 
LVL 1

Expert Comment

by:worldserga
ID: 13993729
Hi

If you need to delete duplicated rows you can do something like that:

SELECT INTO tempTable DISTINCT Distrikt, Kund, ProdNr from yourTable
TRUNCATE table yourTable
INSERT INTO yourTable SELECT * FROM tempTable
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 13993878
Great - auke&steve I'll split those 125 pts btw you two.

I called my client and they now informed me that the uniqueness is only guaranteed when using 4 columns (thanks for telling me that now). Anyway, those three columns correspond to one order. Now I would like to give all orders in the system an ID, i.e. add an OrderID for each row in the table, with those 3 columns in common. Which is the most effective way of doing this?

cheers,

henrik
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

577 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