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

So I was trying to understand how ON DUPLICATE KEY UPDATE works. I want to add entries to a database so I naturally want to check if there are duplicates. If there are no duplicates then I want to insert, but if there are duplicates then I want the id/primary key for that entry. I read in another thread you can invoke the LAST_INSERT_ID; however, that only returns value if it inserted an entry. 
sub insert_entry {
    my $table=$_[0];    #table
    my $col=$_[1];
    my $entry=$_[2];    #value
    
    my $insert=sprintf(qq(INSERT INTO %s(%s) VALUES(%s) ON DUPLICATE K
+EY UPDATE id=LAST_INSERT_ID(id)),
             $dbh->quote_identifier($table),
             $dbh->quote_identifier($col),
             $dbh->quote($entry));
    my $sth=$dbh->prepare($insert);
    $sth->execute();
    
    if ($sth->last_insert_id()){ #if new insertion
         return;
    }
    else {
         return $sth->last_insert_id(); #if duplicate return key
    }    
}

[download]

Replies are listed 'Best First'.
Re: return primary key if duplicate entry exists?
by CountZero (Bishop) on Jan 24, 2016 at 09:36 UTC
    That SQL will not warn you about duplicate keys.

    From the MySQL docs:

    If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.
    In other words, the database will delete the existing entry and replace it with the data in the UPDATE statement.

    If you wish to check if the key already exists then you simply issue a SELECT statement on that key and see if it returns any result.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
[reply]
[d/l]
[select]
      I see. I just assumed if SQL is already looking for a duplicate then maybe it can also just give me the id/key of the row. Seems inefficient to have that as separate steps. Since they have last_insert_id, I assumed they would have a function similar.
[reply]
        actually couldn't I allow it to replace the duplicate with the duplicate and that way last_insert_id will return that row id for me?
[reply]

Back to Seekers of Perl Wisdom