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'
";
[download]

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');
[download]

Making your code readable is a large step towards finding errors.

Abigail

In answer to your direct question "where am I going wrong?", I suspect that one of the variables that you are interpolating into your statement contains a comma.

Have you tried printing the SQL statement that you create? Have you tried executing that statement directly against the database using a command line utility supplied with your RDBMS software?

first reformat your statement:


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."'
";
[download]


then rdfields is completey right:
>   There is no from clause in an update statement (except in sub-queries).
So what do you actually want to do?
pelagic

-------------------------------------
I can resist anything but temptation.

There is no from clause in an update statement (except in sub-queries).

There isn't in SQL92, but Sybase and MS-SQL's Transact-SQL has that extension.

Michael

... and what would that mean in terms of above sql-stmt?
pelagic

-------------------------------------
I can resist anything but temptation.

You're skipping a very important step. Once you've assembled $up, print it out and inspect it by eyeball. Even if you can't see where your code is producing syntactically invalid SQL, you've still reduced the problem by taking Perl out of the mix.

Maybe you did that, but your question doesn't suggest that is so. In particular, you didn't show us an assembled SQL query that demonstrates the problem.

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]

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]

HTH.

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