http://qs321.pair.com?node_id=329885


in reply to SQL Update Error

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

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?

rdfield

Replies are listed 'Best First'.
Re: Re: SQL Update Error
by pelagic (Priest) on Feb 18, 2004 at 12:51 UTC
    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."' ";

    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.
Re: Re: SQL Update Error
by mpeppler (Vicar) on Feb 18, 2004 at 15:24 UTC
    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.
        It wouldn't mean anything. The FROM clause isn't needed here but having it isn't an error in T-SQL. But, the OP didn't say MS SQL Server was being used so who knows.

        Like other posters said, it looks like the problem is with quoting. I'm also not sure what's going on with all the '.' characters all over the place. I recommend printing out $up. If you don't see an obvious error, cut and paste it to the query analyzer and run it. The SQL Server will surely tell you if there is a problem.

        The code below may be closer to what you need.

        my $up = qq/ 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 WHERE Aggregated_area LIKE '$Aggregated_area_fill' AND Cause LIKE '$Cause_fill' /;
        As monktim says - in this particular case it serves no purpose. You'd use that form to limit the rows that are updated, or fetch data with which you wish to update the target table with a join, like this:
        update the_table set col1 = t2.col2 from the_table t1 , other_table t2 where t2.id = t1.id and t1.somecol = "some value" and t2.anothercol = "some other value"
        Having used Sybase for 15+ years I'm really used to this syntax, and I find it quite powerful (and you can do the same thing with DELETE of course).

        Michael