Your code is very very hard to read. I have reformatted it as the following to make the structure more apparent:
my $up = "UPDATE Prior_to_CI_calc_A_$region
SET SUM_wi = $SUM_Weight_wi,
Sum_wixi_x_1000 = $SUM_wixi_x_1000_fill,
Sum_wi2xi_x_1000 = $SUM_wi2xi_x_1000_fill
FROM Prior_to_CI_calc_A_$region A
WHERE A.Aggregated_area LIKE '$Aggregated_area_fill'
AND A.Cause LIKE '$Cause_fill'
";
You are creating SQL on-the-fly and I'm not sure your SQL syntax is correct, as I don't know the combination statement modifiers you use. I think that your error is most likely because one of your several embedded variables is empty or undefined. I recommend that you follow one of the two following paths:
- Implement full SQL statement logging. Whenever you submit any query to your SQL server, log the full query into a logfile. DBI has logging facilities that you can use for that.
- Switch your queries from embedded values to queries using placeholders. I think that Super Search should give you a lot of examples, and your above query would be split up like the following:
sub update_region {
my ($region) = @_;
my $table = "Prior_to_CI_calc_A_$region";
sprintf "UPDATE %s
SET SUM_wi = ? ,
Sum_wixi_x_1000 = ? ,
Sum_wi2xi_x_1000 = ?
FROM %s A
WHERE A.Aggregated_area LIKE ?
AND A.Cause LIKE ?
", $table, $table;
};
my $region = "foo";
my $query = $dbh->prepare(update_region($region));
$query->execute(1,2,3,'US','unknown');
|