in reply to SQL Update Error

Placeholders, placeholders, placeholders!

Using placeholders you should be able to do something like this: 

   my $up = qq{
      UPDATE Prior_to_CI_calc_A_.$region
         SET SUM_wi = ?,
             Sum_wixi_x_1000 = ?,
             Sum_wi2xi_x_1000 = ?
       WHERE A.Aggregated_area LIKE ?
         AND A.Cause LIKE ?
   };
   ### Do print here to see what $up contains ###
   print STDERR "\$up=$up\n";
   my $sth = $dbh->prepare($up);
   $sth->execute($SUM_Weight_wi,
                 $SUM_wixi_x_1000,
                 $SUM_wi2xi_x_1000_fill,
                 $Aggregated_area_fill,
                 $Cause_fill);

[download]
I'm not entirely sure if you can use placeholders in the WHERE like I've shown here. Also I haven't used any error trapping here, and you definitely should - read about RaiseError and PrintError.

You can also use '$dbh->trace(2);' to see what sql is being sent to the database. You should definitely read the excellent DBI perldocs if you haven't already - by doing 

   perldoc DBI

[download]
at a command prompt.

HTH.

Replies are listed 'Best First'.
Re: Re: SQL Update Error
by Grygonos (Chaplain) on Feb 18, 2004 at 13:49 UTC
    You can put place holders in the where clause. However you can't place hold the field name .. only it's value. for instance you couldn't say... 
    my $query = q{SELECT *
              FROM my_table
              WHERE ? LIKE ?};

    [download]
    however 
    my $query = q{SELECT *
              FROM my_table
              WHERE my_field LIKE ?};

    [download]
    will be valid, so long as your placeholder contains the appropriate like operator ie % or whatever is appropriate for your RDBMS
    Grygonos
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom