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.

The DBI supports running single SQL statements per call to execute. This looks like a single statement to me. You should read up in the documentation of DBI. — simbabque
– simbabque, Commented Dec 12, 2013 at 8:37
Sorry, I didn't understand your words clearly. My statement have some if-else logic better handled by the SQL server engine. I didn't expect to include the logic into Perl lines. I heard prepare is expensive considering I have hundreds of rows every time calling in the @$va_arr to loop. — anaconda_wly
– anaconda_wly, Commented Dec 12, 2013 at 8:59
What version of SQL Server? Are you able to "upsert" via MERGE? — pilcrow
– pilcrow, Commented Dec 12, 2013 at 13:17

Community · Accepted Answer · 2017-05-23 10:32:18Z

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.

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Dec 12, 2013 at 11:23

simbabque

54.4k8 gold badges77 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

anaconda_wly Over a year ago

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?

simbabque Over a year ago

I'm not sure I got this. If the query changes (meaning the left side of = or like), you need to reprepare it. If you just have a variable on the right side of a condition, you can put a ? there and do not need to reprepare.

anaconda_wly Over a year ago

Forget that. For another issue: in fact, as I had tried, the execute function should be called like this: $dbh->execute($values->{col_a }, $values->{col_b }, $values->{col_a }, $values->{col_b }); The binding value should be listed one by one, even they're repeated in Insert and Update the same sequence. Or the Perl interpreter will report error pointing out the number of placeholder and the binding values are not match.

simbabque Over a year ago

Ah, damn it. Of course you need to put them in two times. I forgot that, will add it now. Sorry.

josepn · Accepted Answer · 2013-12-12 10:52:54Z

0

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();

answered Dec 12, 2013 at 10:52

josepn

3674 silver badges5 bronze badges

1 Comment

simbabque Over a year ago

Your comment about prepare outside the loop is good, but why did you break up the logic the OP has in his SQL query? The SQL Server is able to optimize this, so there is no need to do additional queries. Also, using do will prepare each time inside the loop.

runrig · Accepted Answer · 2013-12-13 18:59:26Z

0

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

Bulk load the data into a staging table.
Delete all data that joins to the target table.
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;

answered Dec 13, 2013 at 18:59

runrig

6,5442 gold badges31 silver badges46 bronze badges

Collectives™ on Stack Overflow

Perl sql query statement

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related