Re: Load duplicates

--0000000000008aae1906030b4fa3
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Geoffrey,
Thank you for that info. Working on getting it in.
Very helpful.

Best regards.

On Tue, Aug 15, 2023 at 1:34=E2=80=AFPM Geoffrey Rommel <[email protected]=
> wrote:

> So are you reading the records one at a time through DBI and looking for
> duplicates? There is probably a better way. =F0=9F=98=81
>
> 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_nu=
m.
>
> 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=C3=A0 ... 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 sourc=
e,
> 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=E2=80=AFAM Ian <[email protected]> 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 =3D "auto_increment", acct, comp,accr,desc,etc....  :
>>  (auto_increment created when records are added, rest is the source
>> record)
>> Duplicate table fields =3D "auto_increment from MAIN table",
>> acct,comp,accr,desc,etc.....
>>
>> Thanks
>> Ian
>>
>>
>>

--=20
Ian

--0000000000008aae1906030b4fa3
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Geoffrey,<div>Thank you for that info. Working on getting =
it in.</div><div>Very helpful.</div><div><br></div><div>Best regards.</div>=
</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">=
On Tue, Aug 15, 2023 at 1:34=E2=80=AFPM Geoffrey Rommel &lt;<a href=3D"mail=
to:[email protected]">[email protected]</a>&gt; wrote:<br></div><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>So are you r=
eading the records one at a time through DBI and looking for duplicates? Th=
ere is probably a better way. =F0=9F=98=81</div><div><br></div><div>I work =
with Teradata, so I&#39;ll use Teradata syntax, but the syntax for other da=
tabases will be similar.</div><div><br></div><div>To find the duplicates in=
 your table, you can select all columns from the table (except the auto-inc=
rement) along with a count, like so:</div><div><br></div><div>create volati=
le table ##dup_counts</div><div>as</div><div>(select acct, comp, accr, desc=
rip, all-other-columns..., min(auto_increment) min_num /* or max() */ , cou=
nt(*) kount</div><div>from table1</div><div>group by=20
acct, comp, accr, descrip, all-other-columns... )</div><div>with data;</div=
><div><br></div><div>Now there are no duplicates in ##dup_counts, so you ca=
n insert them to your target table, either generating a new auto_increment =
or using min_num.</div><div><br></div><div>The duplicates can now be insert=
ed into your error table:</div><div>insert into error_table</div><div>selec=
t acct, comp, ...etc.</div><div>from ##dup_counts</div><div>where kount &gt=
; 1;</div><div><br></div><div>Et voil=C3=A0 ... the records are deduped wit=
h only three statements.</div><div><br></div><div>As you probably know, in =
relational databases rows do not have an order, so I&#39;m not sure that yo=
u necessarily need a sequence number on the duplicates. If you want to trac=
e the duplicates back to a flat file source, it might be better to add a se=
quence number to the file before loading it to the database.</div><div><br>=
</div><div>I hope this helps.</div><br></div><br><div class=3D"gmail_quote"=
><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Aug 15, 2023 at 11:50=E2=80=
=AFAM Ian &lt;<a href=3D"mailto:[email protected]" target=3D"_blank">pcs305@=
gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div dir=3D"ltr">Hi group,<div><br></div><div>My perl skills are=
 basic and my SQL skills almost match that.</div><div>Using perl 5.28 and m=
ysql on windows.</div><div><br></div><div>I have a couple million records t=
hat needs processing to go to their final destination.</div><div>Currently =
I&#39;m catchin duplicates with error 1062 in perl and using that to write =
the duplicate records to a separate=C2=A0table for later processing.<br></d=
iv><div><br></div><div>Question: Can DBI give me a field from the record on=
 the main table causing the duplicate so I can add=C2=A0that to the record =
going to the=C2=A0duplicate table?</div><div><br></div><div>Example:</div><=
div>MAIN table fields =3D &quot;auto_increment&quot;, acct, comp,accr,desc,=
etc....=C2=A0 :</div><div>=C2=A0(auto_increment created when records are ad=
ded, rest is the source record)</div><div>Duplicate table fields =3D &quot;=
auto_increment from MAIN table&quot;, acct,comp,accr,desc,etc.....</div><di=
v><br></div><div>Thanks</div><div>Ian</div><div><br></div><div><br></div></=
div>
</blockquote></div>
</blockquote></div><br clear=3D"all"><div><br></div><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><d=
iv dir=3D"ltr">Ian<br></div></div>

--0000000000008aae1906030b4fa3--