If that won't work for you, then you may want to use SQL::Parser to parse each line in the SQL file, that will turn your UPDATE statements into perl data stuctures containing the names of the table, the fields, and the values and you can use that to construct an INSERT statement where needed.

If you go the route of using regexen to read the SQL file, the complexity of that task will depend on the complexity of your string values - if any of them contain embedded single quotes, you'll need to get fancy to deal with the single-quote delimiters around the values.

You still have to write the matching UPDATE and INSERT, but it's written so you can switch between the two easily:

$upd_sql = "UPDATE table SET [Field1]=?, [Field2]=?, [Field3]=? where 
+Field0=?";
$ins_sql = "INSERT INTO table ( [Field1], [Field2], [Field3], [Field0]
+ ) VALUES ( ?, ?, ?, ? )";

$sth = $dbh->prepare($exists ? $ins_sql : $upd_sql);
$sth->execute(
   'stringvalue',
   numericvalue,
   booleanvalue,
   index,
);
[download]

This looks nice, especially if it does what I think it should. Currently, the scenario I need this for is when I want to allow someone to update ( UPDATE) a record through an HTML form, as opposed to entering it for the first time in the form (INSERT). I redirect passed on a hidden field in my form that is set to edit or new.

My question is: where does $exist come from in your prepare statement? Thanks.


—Brad
"Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton

Out of thin air, just like it wasn't in your code. Usually, you already know if you're updating or inserting a record on what the user wants to do. If you don't know, you could try to do an INSERT, and if that fails, try to do an UPDATE.

I pulled the following piece of code from a CGI I wrote a while back:

  my $Cmd;
  if ( $Vars->{ id } == 0 )     #  Insert a new record.
  {
    $Cmd = "insert into fparty (" .
      join(", ",@Fields) . ") values(" .
      join ( ", ", map { "'$Vars->{ $_ }'" } @Fields ) . ")";
  }
  else                          #  Update an existing record.
  {
    $Cmd = "update fparty set " .
      join ( ", ", map { $_='$Vars->{ $_ }'" } @Fields ) . 
      " where id=$Vars->{ id }";
  }
[download]

This piece of code should use placeholders, but it builds the appropriate INSERT or UPDATE based on whether the id number of the object it's working on exists or not.

If you're really stuck with taking the INSERT statement and building an UPDATE statement out of it, you're going to have to parse it somehow -- and it looks like you have some suggestions on how to do that.

open(FILE, "./logfile.sql");
my %foo;

while (<FILE>) {

     my $index;
    
     if (m/Field0=(.*)\;/) {
       
         $index = $1;
     } else {

         # something didn't match correctly
         $index ="";
     }

     $foo{$index}++;
     if ($foo{$index} == 1) {

         # make insert statment

      } else {
         # make update statement.

      }

     

}
[download]

That is only going to work if every single column of every single row in that table has been updated recently (within your log). You will not be able to recreate rows or columns within rows that have not been updated.

The only time I want to change an Update statement into an Insert statement is the first time the key shows up

You can just go ahead and try to update. If the update reports 0 affected rows (DBI->do tells you this), you have to change it to an insert and run it again. This way, you can let the DB keep track of "first times".

The second problem is that I need to be able to completely restructure the command

You could get away with a simplified insert (which just creates the row) and then running the original update. That would reduce the amount of intelligence you have to put into that update-to-insert query rewriter.

But, why would you use Perl? Why not see if the database you're using can recreate the table from a series of Update statements ... I'm pretty sure some of them have utilities to do this ...