Midrange News for the IBM i Community

Posted by: neilrh
Jackson, MI
Find duplicate records using SQL
has no ratings.
Published: 14 Jul 2014
Revised: 21 Jul 2014 - 1914 days ago
Last viewed on: 16 Oct 2019 (3423 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Find duplicate records using SQL Published by: neilrh on 14 Jul 2014 view comments(6)

Many times in my work I'm sent a spreadsheet with a "Please load to production" note. The problem is that frequently they don't check all of the data and we end up trying to add duplicates into the production files. Now while it's possible to run an INSERT INTO ..... WHERE NOT EXISTS deal, in some cases we need to run this multiple times until the records are loaded. And there's no early warning you're just discarding records arbitrarily.

I've begun to use the following SQL to find duplicate entries, prior to attempting to add the records, and I can even go back to the spreadsheet - highlight the duplicates and ask which is correct!

with stuff as (select rrn(mf) as recno, mf.* from mylib/myfile mf)
select * from stuff a left join stuff b on a.f1 = b.f1 [and a.f2 = b.f2....]
where a.recno > b.recno

just remember to join on all of the primary keys.

Return to midrangenews.com home page.
Sort Ascend | Descend