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


T SQL to clean up tables

Posted on 2011-05-10
Medium Priority
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
  • 3
  • 2
LVL 26

Expert Comment

ID: 35733027
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

ID: 35733086
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

tigin44 earned 2000 total points
ID: 35733158
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 26

Expert Comment

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

Expert Comment

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

Author Closing Comment

ID: 35738069
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!

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

569 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