need some newbie logic help on Insert/Update

[Tim Carlson]

Hi,

I'm writing some code to transfer data from Oracle into Mysql on a nightly
basis. I have no problems connecting to the databases and doing inserts or
updates. My problem is with figuring out when to do an insert vs and
update without looping through a bunch of data. Here is some of my code. I
use the same table names and column names in each database.

------------------------
use DBI;


# First grab data from our Oracle database

my $dbh = DBI->connect($oracle_db,$user,$password,
        {RaiseError => 1, PrintError => 1, AutoCommit => 1} ) or
        die "Unable to connect: " . $DBI::errstr . "\n";
my $sel = $dbh->prepare( "select * from SOME_TABLE" );
$sel->execute();

# Now connect to the mysql database

my $mysql_dbh = DBI->connect($mysql_db,$mysql_user,$mysql_password,
        {RaiseError => 1,PrintError => 1, AutoCommit => 1} ) or
        die "Unable to connect: " . $DBI::errstr . "\n";

# First a template for an Update
my $mysql_upd = $mysql_dbh->prepare(
        "UPDATE SOME_TABLE set FIRST_NAME=? where ID=?" );

# Now a template for an Insert if this is new ID
my $mysql_ins =$mysql_dbh->prepare(
        "INSERT into  SOME_TABLE(FIRST_NAME,ID) values(?,?)" );



# Now here is where I have a problem. I could do the following for an
# new records

my $row_hash;
while($row_hash = $sel->fetchrow_hashref) {
    $mysql_ins->execute($row_hash->{FIRST_NAME},$row_hash->{ID})
        }

#Or I could do the following for an update

my $row_hash;
while($row_hash = $sel->fetchrow_hashref) {
    $mysql_upd->execute($row_hash->{FIRST_NAME},$row_hash->{ID})
        }

$sel->finish;
$dbh->disconnect;
$mysql_dbh->disconnect;
--------------------------------------------


Now do I have to loop through the MySQL table seeing if I have to do an
INSERT or an UPDATE?  Any cool shorthand I can use to do this test?

Thanks

Tim Carlson
Voice: (509) 376 3423
Email: [EMAIL PROTECTED]
EMSL UNIX System Support