Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

How do I avoid inserting duplicate numbers into an Access table?

by luoina (Acolyte)
on Mar 06, 2004 at 05:29 UTC ( #334454=perlquestion: print w/replies, xml ) Need Help??

luoina has asked for the wisdom of the Perl Monks concerning the following question:

Dear All: I was trying to insert some numbers into an Access table. There were already some numbers in this table. I did not want to insert those numbers that already in the table. So I first read the number into an array named @IDnumber. Then I use the following code,
............ $ua=new LWP::UserAgent; $ua->timeout ([180]); $request = new HTTP::Request('GET', $url); $response=$ua->request($request); if (!($response->is_success)) { print " Error can not load! \n "; } ......... NEXTLINE1: while ($CONTENDS=~ /\d{10}/g) { $match=$&; $CONTENDS=$'; $count=0; while ($count<=@IDnumber) { if ($match == $IDnumber[$count]){ goto NEXTLINE1; } $count++; } push (@IDnumber1, $match); } ............ for ($Inde=0;$Inde<=@IDnumber1;$Inde++){ my $match=$IDnumber1[$Inde]; $sqlinsert="INSERT INTO $tab[$loop] VALUES('$match')"; $rc=$db->Sql($sqlinsert); die qq(SQL fail "$rc":), $db->Error(),qq(n) if $rc; }
But I did not succeed. Duplicated numbers were inserted into the table. Could anybody help me to figure out whats wrong with my code? Thank you very much in advance. .... Luoina

Replies are listed 'Best First'.
Re: How do I avoid inserting duplicate numbers into an Access table?
by bmann (Priest) on Mar 06, 2004 at 06:39 UTC
    This is a constraint that is best handled in the database itself, not in the code. If you have control over the structure of the database, create a unique index on the field. In access, I believe it's a combo box called "Indexed" - select Yes, No duplicates. In SQL, it'll look like "CREATE UNIQUE INDEX name ON table (column)". Either way, the db will prevent duplicate records.

    If (and only if) you have no control over the structure of the database, it's a 2-step procedure. Query the table for records that match (select * where field='no duplicates'). If you get no results, then insert the record. And be wary of race conditions if the db is multiuser!

Re: How do I avoid inserting duplicate numbers into an Access table?
by castaway (Parson) on Mar 06, 2004 at 06:39 UTC
    The code could be tidied up a little but it looks like it should work. It isn't checking anything that might already be in the table, however, just the set of data you are currently inserting, is that intentional?

    Some tips:
    You can replace:

    while ($count<=@IDnumber) { if ($match == $IDnumber[$count]){ goto NEXTLINE1; } $count++; }
    next if(grep {$_ eq $match} @IDnumber);
    and you don't really need the label/goto, the command 'next' does exactly that, ie jump back to the top of the loop and start the next iteration.

    As to the IDs, first, you should probably check the current values in the table, to see if the one you want to insert is present, assuming there are any. But a much easier way to do this would be to make the field in the table unique ('no duplicates', in Access), in which case it will simply refuse to insert that line, and you can carry on to the next.


      Hi there, thank you for your advice. I really learned from what you wrote. But I still have some prolem. When I was trying to set the Access table to refuse duplicate values, the program simply stopped and gave error message when there were duplicated values. So I want to first read what are already in the table into an array, lets say array A1, and compare the values I intent to insert into the table with the values in A1. If a value has already been in A1, I will discard it and go to the next value. If the next value is not in A1, I will put it into an array A2. After this process ends, I then will insert the values in A2 into the Access table. My problem is that the values in A2 contains some values in A1. I do not know why.
        Here is the line that is exiting your script:

        die qq(SQL fail "$rc":), $db->Error(),qq(n) if $rc;

        Just handle the error differently. Check $rc or $db->Error(), and continue with the next insert if the error points to a duplicate value.

        # untested if ($rc == WHATEVER_ERROR_IS_GENERATED_BY_DUPLICATE) { # you can also test ($db->Error() =~ /some value/) next; } else { die qq(SQL fail "$rc":), $db->Error(),qq(n); }
        Your script will still die if the insert fails for a different reason.


        Access has a query wizard to find duplicate values in a table.
        First, add an autoincrement column to the table like others suggested.
        Second, run the Find Duplicates query wizard to generate a query that finds all the duplicate rows.

        Use that Find Duplicates query as the basis to generate and fine tune a Delete query that gets rid of the duplicate rows by eliminating the rows having an autoincrement value higher the lowest one found for each duplicate.

        Then delete the autoincrement column from the table and then re-add it again. This should work as long as you haven't used the column or table in a Relationship.

        Now you should have consecutively numbered rows with clean nonduplicated data in a table that generates it's unique row autocount ID automatically.

Re: How do I avoid inserting duplicate numbers into an Access table?
by matija (Priest) on Mar 06, 2004 at 06:52 UTC
    First of all, you can often get the database itself not to accept any duplicate numbers, if you define that table with a unique index over the number. That will work much better than doing it on your side, particularly when the table gets big.

    Now to your code.

    • When you're looking for a number like that, you don't need to use $&, simply enclose what you're looking for in ().
    • You don't need $', because Perl will keep track of where it was in the string.
    • You don't need to scan continously over an array to see if a number is there, that's what hashes are for.
    • You don't need the GOTO, because Perl provides the next keyword.
    I left your variable names as they were, so that you will find it easier to compare your code to mine. But in general, all caps for a variable name is rare in Perl. Oh, and contends should probably be contents...
    while ($CONTENDS=~/(\d{10})/g) { $match=$1; $IDNUMBER{$match}=1 unless $IDNUMBER{$match}; } foreach $match (keys %IDNUMBER) { $sqlinsert="INSERT INTO $tab[$loop] VALUES('$match')"; $rc=$db->Sql($sqlinsert); die qq(SQL fail "$rc":), $db->Error(),qq(n) if $rc; }
      The reason why you should try to avoid $` and use capturing parentheses in regexes is that it really slows down your regex processing.

      The same goes for $ and (but to a lesser degree) $&.

      Once you use them anywhere in your script all your regex-processing slows down, even those who do not use these variables.


      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

      Thank you so much. I am a starter in Perl. What you said help me a lot.....Luoina
Re: How do I avoid inserting duplicate numbers into an Access table?
by Zero_Flop (Pilgrim) on Mar 06, 2004 at 06:27 UTC
    I have not tried this but not knowing any better here is how I would approch it.

    I am assuming that the feild that you do not want to have duplicate values in is your unique identifier for each record. If this is the case you want to have Access AutoGenerate the number for that field when it is created. You set this up when you set up that field in the Table.

    Now when you want to add a field through automation. Have access create the record then fill in the other data. You should not have to worry about that field

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://334454]
Approved by Daruma
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2022-06-26 11:45 GMT
Find Nodes?
    Voting Booth?
    My most frequent journeys are powered by:

    Results (85 votes). Check out past polls.