MySQL and Perl, getting the PRIMARY after an INSERT IGNORE

I'm using Perl with the DBI module to insert a row to a table. The table has 3 columns: id (which is a PRIMARY and autoincrement), then colA (unique) and colB (text).

$dbh->do("INSERT IGNORE INTO table (colA, colB) VALUES (?, ?), undef,
 'foo',
 'bar') or die "Couldn't execute INSERT INTO table: " . $dbh->errstr;

What I want to find is either the new ID if the insert happens, or the old matching one if it didn't.

The only way that I can think to do this is with a second query:

$dbh->do("INSERT IGNORE INTO table (colA, colB) VALUES (?, ?), undef,
 'foo',
 'bar') or die "Couldn't execute INSERT INTO table: " . $dbh->errstr;

$id = $dbh->{'mysql_insertid'};

if (!$id) {
 my ($id) =
  $dbh->selectrow_array("SELECT id FROM table WHERE colA = ? LIMIT 1", undef,
   'foo') or die "Couldn't execute SELECT FROM table: " . $dbh->errstr;
}

Can you guys and gals suggest a way to get the ID without the second query? In production I'll be doing 4 separate inserts (with each one referring to an ID from a previous insert), so doing it this way would be a potential of 8 queries!

MySQL and Perl, getting the PRIMARY after an INSERT IGNORE

csdude55

topr8

Demaestro

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week