Geoffrey, Thank you for that info. Working on getting it in. Very helpful. Best regards.
On Tue, Aug 15, 2023 at 1:34 PM Geoffrey Rommel <wgrom...@gmail.com> wrote: > So are you reading the records one at a time through DBI and looking for > duplicates? There is probably a better way. 😁 > > I work with Teradata, so I'll use Teradata syntax, but the syntax for > other databases will be similar. > > To find the duplicates in your table, you can select all columns from the > table (except the auto-increment) along with a count, like so: > > create volatile table ##dup_counts > as > (select acct, comp, accr, descrip, all-other-columns..., > min(auto_increment) min_num /* or max() */ , count(*) kount > from table1 > group by acct, comp, accr, descrip, all-other-columns... ) > with data; > > Now there are no duplicates in ##dup_counts, so you can insert them to > your target table, either generating a new auto_increment or using min_num. > > The duplicates can now be inserted into your error table: > insert into error_table > select acct, comp, ...etc. > from ##dup_counts > where kount > 1; > > Et voilà ... the records are deduped with only three statements. > > As you probably know, in relational databases rows do not have an order, > so I'm not sure that you necessarily need a sequence number on the > duplicates. If you want to trace the duplicates back to a flat file source, > it might be better to add a sequence number to the file before loading it > to the database. > > I hope this helps. > > > On Tue, Aug 15, 2023 at 11:50 AM Ian <pcs...@gmail.com> wrote: > >> Hi group, >> >> My perl skills are basic and my SQL skills almost match that. >> Using perl 5.28 and mysql on windows. >> >> I have a couple million records that needs processing to go to their >> final destination. >> Currently I'm catchin duplicates with error 1062 in perl and using that >> to write the duplicate records to a separate table for later processing. >> >> Question: Can DBI give me a field from the record on the main table >> causing the duplicate so I can add that to the record going to >> the duplicate table? >> >> Example: >> MAIN table fields = "auto_increment", acct, comp,accr,desc,etc.... : >> (auto_increment created when records are added, rest is the source >> record) >> Duplicate table fields = "auto_increment from MAIN table", >> acct,comp,accr,desc,etc..... >> >> Thanks >> Ian >> >> >> -- Ian
