HoursIn 
6313,20150121095900,23147 
 
MasterFile 
0006313,20120524072600,1000 
6313,20120120072500,2000 
 
doupdate.pl 
#!/usr/bin/perl 
 
use DBI; 
use strict; 
use Cwd; 
 
my ($StartDir); 
my ($XMASTER_query, $XMASTER_st, $XMASTER_db); 
my ($StartDir,$ProcessMask); 
my ($IndiFile); 
my ($HOURS_query, $HOURS_st, $HOURS_db); 
my ($SerialNumber, $DateTime, $LifeHours); 
my ($cYear, $cMon, $cDay, $cHour, $cMin, $cSec); 
 
    $XMASTER_db = DBI->connect("DBI:CSV:f_dir=$StartDir") 
        or die "Cannot connect: " . $DBI::errstr; 
 
    $XMASTER_db->{'csv_tables'}->{'XMASTER'} = { 
        'eol' => "\n", 'sep_char' => ",", 'quote_char' => undef, 
        'escape_char' => undef, 'file' => 'MasterFile', # <-the CSV file 
        'col_names' => 
     [ "SerialNumber", "DateTime", "LifeHours" ] 
    }; 
 
   $StartDir="/home/mes_cron"; 
   $IndiFile="HoursIn"; 
 
    $HOURS_db = DBI->connect("DBI:CSV:f_dir=$StartDir") 
        or die "Cannot connect: " . $DBI::errstr; 
 
    $HOURS_db->{'csv_tables'}->{'HOURS'} = { 
        'eol' => "\n", 'sep_char' => ",", 'quote_char' => undef, 
        'escape_char' => undef, 'file' => $IndiFile, # <-the CSV file 
        'col_names' => 
     [ "SerialNumber", "DateTime", "LifeHours" ] 
    }; 
 
   $HOURS_query = "SELECT SerialNumber,DateTime,LifeHours FROM HOURS"; 
 
   $HOURS_st = $HOURS_db->prepare($HOURS_query); 
   $HOURS_st->execute(); 
   $HOURS_st->bind_columns(undef, \$SerialNumber, \$DateTime, \$LifeHours); 
 
   while ($HOURS_st->fetch) 
   { 
      &insert_or_update( $SerialNumber, $DateTime, $LifeHours ); 
   } 
   $HOURS_st->finish(); 
 
   $HOURS_db->disconnect(); 
 
   $XMASTER_db->disconnect(); 
 
   exit(0); 
 
#------------------------------------------------------------------ 
 
sub insert_or_update { 
   my ($SerialNumber, $DateTime, $LifeHours ) = @_; 
 
   if ( &exists_in_master( $SerialNumber ) ) 
   { 
     update_master($SerialNumber,$DateTime,$LifeHours); 
   } 
   else 
   { 
     insert_master($SerialNumber,$DateTime,$LifeHours); 
   } 
 
} 
 
#------------------------------------------------------------------ 
 
sub insert_master { 
   my ($SerialNumber, $DateTime, $LifeHours ) = @_; 
   my ($col_str, $val_str, $sql_str); 
 
   $col_str = "SerialNumber, DateTime, LifeHours"; 
 
   $val_str = "\'$SerialNumber\', \'$DateTime\', \'$LifeHours\'"; 
 
   $sql_str = "INSERT INTO XMASTER ( $col_str ) VALUES ( $val_str )"; 
 
   $XMASTER_db->do($sql_str); 
} 
 
#------------------------------------------------------------------ 
 
sub update_master { 
   my ($SerialNumber, $DateTime, $LifeHours ) = @_; 
 
   $XMASTER_db->do("UPDATE XMASTER                                         \ 
                       SET DateTime='$DateTime', LifeHours='$LifeHours'  \ 
                       WHERE ( SerialNumber='$SerialNumber' AND            \ 
                               LifeHours<'$LifeHours' AND                  \ 
                               DateTime<'$DateTime' )               "); 
} 
 
#------------------------------------------------------------------ 
 
sub exists_in_master { 
   my ($SerialNumber, $ActionType ) = @_; 
   my ($retval); 
 
   $XMASTER_query = "SELECT SerialNumber FROM XMASTER \ 
                             WHERE SerialNumber='$SerialNumber' "; 
 
   $XMASTER_st = $XMASTER_db->prepare($XMASTER_query); 
   $XMASTER_st->execute(); 
   $XMASTER_st->bind_columns(undef, \$SerialNumber ); 
 
   if ( $XMASTER_st->fetch ) 
   { 
      $retval = 1; 
   } 
   else 
   { 
      $retval = 0; 
   } 
 
   $XMASTER_st->finish(); 
 
   return($retval); 
} 
 
#------------------------------------------------------------------

use warnings FATAL => qw(all);

sub update_master { 
   my ($SerialNumber, $DateTime, $LifeHours ) = @_; 
   my ($newSQLstr); 
 
   $newSQLstr = sprintf ("UPDATE XMASTER                                         \ 
                       SET DateTime='%s', LifeHours='%s'  \ 
                       WHERE ( SerialNumber='%s' AND            \ 
                               LifeHours<'%s' AND                  \ 
                               DateTime<'%s' )               ", 
                       $DateTime, $LifeHours, 
                       $SerialNumber,$LifeHours,$DateTime); 
   print "$newSQLstr\n"; 
   $XMASTER_db->do($newSQLstr); 
}

UPDATE XMASTER 
                       SET DateTime='20150121095900', LifeHours='23147' 
                       WHERE ( SerialNumber='6313' AND 
                               LifeHours<'23147' AND 
                               DateTime<'20150121095900' )

UPDATE XMASTER 
                       SET DateTime="20150121095900", LifeHours="23147" 
                       WHERE ( SerialNumber="6313" AND 
                               LifeHours<"23147" AND 
                               DateTime<"20150121095900" )

DBD::CSV::db do failed: Can't find table containing column named '"6313"' at /us 
 [for Statement "UPDATE XMASTER 
                       SET DateTime="20150121095900", LifeHours="23147" 
                       WHERE ( SerialNumber="6313" AND 
                               LifeHours<"23147" AND 
                               DateTime<"20150121095900" )               "] at .

sub update_master { 
   my ($SerialNumber, $DateTime, $LifeHours ) = @_; 
   my ($newSQLstr); 
 
  $XMASTER_st = $XMASTER_db->prepare(qq{UPDATE XMASTER 
                       SET DateTime= ? , LifeHours= ? 
                       WHERE ( SerialNumber= ? AND 
                               LifeHours< ? AND 
                               DateTime< ? ) 
  }); 
  $XMASTER_st->execute( "$DateTime", "$LifeHours", 
                       "$SerialNumber","$LifeHours","$DateTime"); 
}

sub update_master { 
   my ($SerialNumber, $DateTime, $LifeHours ) = @_; 
   my $length = length($SerialNumber); 
    
   $XMASTER_db->do("UPDATE XMASTER 
                    SET DateTime='$DateTime', LifeHours='$LifeHours' 
                    WHERE ( SerialNumber = '$SerialNumber' AND 
                            CHAR_LENGTH(SerialNumber) = $length AND 
                            LifeHours < '$LifeHours' AND 
                            DateTime < '$DateTime')" 
                  );}