Perl sql query statement

Question

My sql statement is simple as below:

if not exists (select col_a from t_a where co_b = 'v_b')
begin
    insert into t_a (col_a  ,col_b  ) 
        VALUES(v_a,v_b)
end
else
begin
    update t_a set col_a = v_a, col_b = v_b where col_b = 'v_b'
end

As I have hundreds of rows to update, how can I do this in Perl for the least time cost?

If I use Prepare + Execute, how to write the statement using the placeholder ? ?

Does the $dbh->prepare($statement); support multiple composite SQL lines like those above? Or do I have to save the lines into an sql file and run it using SQL server?

To make the question more clear, my Perl lines look like those below:

$statement = "if ... VALUES(?,?)...update t_a set col_a = ?, col_b = ?"; 
# better to use one binding values(v_a, v_b) couplets mapping 
# the 2 placeholders of insert and update both?
foreach (@$va_arr) {
    my $values_for_one_row = $_;            
    $dbh->prepare($statement);
    $execute->execute($values_for_one_row->{col_a }, $values_for_one_row->{col_b });
}

I forgot one thing: the 'whatever' is also a value in $va_arr to be changed on every iteration: if not exists (select col_a from t_a where co_b = 'v_b'). Also, the update section should be: update t_a set col_a = ?, col_b = ? where col_b = "v_b". Seems no better way then include the prepare into the loop? Sorry I didn't think the example complete. But I think simbabque's answer is good enough.

Answer 1

You can use your SQL without problems. You need to prepare the statement once. I am assuming your $va_arr looks like this:

my $va_arr = [
  {
    col_a => 1,
    col_b => 2,
  },
  {
    col_a => 'foo',
    col_b => 'bar',
  },
];

Your code to run this could be as follows. Note that you have to pass the col_n params twice as it needs to fill them in two times into each ? with every execute. They get filled in the order of the ? in the query, so we need col_a, col_b for the INSERT and another col_a, col_b for the UPDATE.

my $sql = <<'EOSQL';
if not exists (select col_a from t_a where co_b = 'whatever')
begin
    insert into t_a (col_a  ,col_b  ) 
        VALUES(?, ?)
end
else
begin
    update t_a set col_a = ?, col_b = ?
end
EOSQL

my $sth = $dbi->prepare($sql);
foreach ($values = @{ $va_arr }) {
  $dbh->execute($values->{col_a }, $values->{col_b }, 
                $values->{col_a }, $values->{col_b });
}

---

If you have a long list of columns and you know the order, consider this:

my @columns = qw( col_a col_b col_c col_n );
my $va_arr = [
  {
    col_a => 1,
    col_b => 2,
    col_n => 99,
  },
  {
    col_a => 'foo',
    col_b => 'bar',
    col_n => 'baz',
  },
];

# build the sql dynamically based on columns
my $sql = q{
if not exists (select col_a from t_a where co_b = 'whatever')
begin
    insert into t_a (} . join(',' @columns) . q{) 
        VALUES(} . join(',', map '?', @columns) . q{)
end
else
begin
    update t_a set } . join(',' map { "$_ => ?" } @columns) . q{
end
};
my $sth = $dbi->prepare($sql);
foreach ($values = @{ $va_arr }) {
  $dbh->execute(@{$values}{@columns}, @{$values}{@columns});
}

Let's look at what this does. It's helpful if you have a really long list of columns.

• You know their names and order, and put that into @columns.
• Build the SQL based on these columns. We have to add the column name and a ? to the INSERT and the combination of both to the UPDATE for each of the columns.
• Execute it with a hash ref slice

Please note that I have not run this, just hacked it in here.

Answer 2

If upsert is not available, here's how I might do it:

1. Bulk load the data into a staging table.

2. Delete all data that joins to the target table.

3. Insert data from staging to target.

Alternatively you can update from staging to target, delete from the staging data that joins, then insert what's left in staging.

Or, a few hundred rows is not that many, so I might: prepare an insert and an update statement handle outside of the loop. Then in the loop:

my $rows = $upd_sth->execute(...);
$ins_sth->execute(...) if $rows == 0;

Answer 3

you should put the prepare statement out of the loop and use a transaction

for example:

my $sql1 = qq(select col_a from t_a where col_b = ?);
my $sql2 = qq(insert into t_a (col_a, col_b) VALUES(?, ?));
my $sql3 = qq(update t_a set col_a = ? where col_b = ?);

my $query = $dbh->prepare($sql1);

$dbh->begin_work();

foreach (@$va_arr) {

    my $values_for_one_row = $_;
    $query->execute($values_for_one_row->{col_b});
    my @out = $query->fetchrow_array();
    $query->finish();

    if ( not defined $out[0] )
      {
        $dbh->do($sql2, undef, $values_for_one_row->{col_a}, $values_for_one_row->{col_b});
      }
      else
      {
        $dbh->do($sql3, undef, $values_for_one_row->{col_a}, $values_for_one_row->{col_b});
      }
}

$dbh->commit();