T SQL to clean up tables

Posted on 2011-05-10
Last Modified: 2012-05-11
I have a database table which is in a bit of a mess and I would like to do some cleanup.

Essentially I have a table ijob and a table iinvoice - ijob contains a field iinvoice_id which points to a record in the iinvoice table or may be NULL.

Each record in the iinvoice table has a field iinvoice_number which is a string.

My problem is that I have duplicate records in the iinvoice table - that is records with the same iinvoice_number pointed at by the ijob table.

As an example - I might have 3 iinvoice records with iinvoice_id set to 100, 101 amd 102 but all with the same iinvoice_number.  In the ijob table I have jobs pointing to 100, 101 and 102.

What I would like is a query which will update ijob so that they link to the first matching entry in the iinvoice table and preferably delete the old records which are no longer required.

I have made a start(?) with

SELECT MIN(iinvoice_id),COUNT(iinvoice_number) AS DUPLICATE FROM iinvoice GROUP BY iinvoice_number ORDER BY DUPLICATE DESC

which identifies where there are duoplicates but not sure where to go next
Question by:ChrisMDrew
    LVL 26

    Expert Comment

    this will remove the repeating rows leaving the first occurence.
    SELECT iinvoice_id,iinvoice_number, ROW_NUMBER() OVER (PARTITION BY iinvoice_number ORDER BY iinvoice_id) AS rowOrder
    INTO #iinvoice
    FROM iinvoice 
    FROM iinvoice I
    	INNER JOIN #iinvoice T ON I.iinvoice_id = T.iinvoice_id
    WHERE T.rowOrder > 1

    Open in new window


    Author Comment

    Thanks - trouble is that iinvoice_id is a foreign key from the ijob to the iinvoice tabkle and as such I cannot delete entries from iinvoice until I have updated all references to them (in ijob)
    LVL 26

    Accepted Solution

    try this to update the ijobs table
    SELECT J.iinvoice_id = T1.iinvoice_id
    SET J.iinvoice_id = T1.iinvoice_id
    -- SELECT J.iinvoice_id, T1.iinvoice_id
    FROM #iinvoice T1
    	INNER JOIN #iinvoice T2 ON T1.iinvoice_number = T2.iinvoice_number
    	INNER JOIN ijobs J ON J.iinvoice_id = T2.iinvoice_id
    WHERE T1.rowOrder = 1
      AND T2.rowOrder <> 1

    Open in new window

    LVL 26

    Expert Comment

    ignore the first line of the code in the last post... a copt past error...
    LVL 21

    Expert Comment

    by:Alpesh Patel
    delete a
    from dbo.iinvoice t a, dbo.iinvoice  b
    where a.iinvoiceno  = b.iinvoiceno
    and a.indent > b. indent

    Author Closing Comment

    Thanks - by combining your two posts I have been able to clean over 2000 invoices from the system.  No way I would have come up with that query!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now