Question regarding FOR UPDATE

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Question regarding FOR UPDATE

Martin Neumann
Hello,

I'm trying to run following SQL query:

SELECT
     ID,
     BROKER_NAME AS B,
     CSV_REG_EX AS C,
     ACTIVE
FROM
     APP.BROKERS
FOR UPDATE OF
     B,
     C,
     ACTIVE;

and get this error.

[Exception, Error code 30,000, SQLState 42X04] Column 'B' is either not
in any table in the FROM list or appears within a join specification and
is outside the scope of the join specification or appears in a HAVING
clause and is not in the GROUP BY list. If this is a CREATE or ALTER
TABLE  statement then 'B' is not a column in the target table.
   Line 1, column 1


Derby 10.15.1.3 is running on openJDK12 within Netbeans 11.0.

When I remove the alias for BROKER_NAME AND CVS_REG_EX it works.

Reading the documentation my understanding is that FOR UPDATE should
also work with aliased columns.

Any advise is appreciated.

Thanks and best regards
Martin

Reply | Threaded
Open this post in threaded view
|

Re: Question regarding FOR UPDATE

Rick Hillegas-3
On 7/22/19 8:10 PM, Martin Neumann wrote:
SELECT
    ID,
    BROKER_NAME AS B,
    CSV_REG_EX AS C,
    ACTIVE
FROM
    APP.BROKERS
FOR UPDATE OF
    B,
    C,
    ACTIVE;

Hi Martin,

Try the final query in the following script:

connect 'jdbc:derby:memory:db;create=true';
CREATE TABLE brokers
(
  id INT,
  broker_name VARCHAR(50),
  csv_reg_ex VARCHAR(50),
  active BOOLEAN
);
-- ERROR 42X04: Column 'B' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B' is not a column in the target table.
SELECT
    ID,
    BROKER_NAME AS B,
    CSV_REG_EX AS C,
    ACTIVE
FROM
    APP.BROKERS
FOR UPDATE OF
    B,
    C,
    ACTIVE;
-- ERROR 42X42: Correlation name not allowed for column 'BROKER_NAME' because it is part of the FOR UPDATE list.
SELECT
    ID,
    BROKER_NAME AS B,
    CSV_REG_EX AS C,
    ACTIVE
FROM
    APP.BROKERS
FOR UPDATE OF
    broker_name,
    csv_reg_ex,
    ACTIVE;
-- succeeds
SELECT
    ID,
    BROKER_NAME,
    CSV_REG_EX,
    ACTIVE
FROM
    APP.BROKERS
FOR UPDATE OF
    broker_name,
    csv_reg_ex,
    ACTIVE;


Reply | Threaded
Open this post in threaded view
|

Re: Question regarding FOR UPDATE

Martin Neumann
Hi Rick,

thanks for the feedback. I tried that basically it confirms my suspicion
that I can't us an alias on columns which I want to update.
I changed my application already to use a separate prepared statement to
make changes to the table.
Would be nice if the documentation would point that out.

Again thanks for taking the time looking into this.

Best regards
Martin

On 7/23/2019 9:54 AM, Rick Hillegas wrote:

> On 7/22/19 8:10 PM, Martin Neumann wrote:
>> SELECT
>>     ID,
>>     BROKER_NAME AS B,
>>     CSV_REG_EX AS C,
>>     ACTIVE
>> FROM
>>     APP.BROKERS
>> FOR UPDATE OF
>>     B,
>>     C,
>>     ACTIVE;
>
> Hi Martin,
>
> Try the final query in the following script:
>
> connect 'jdbc:derby:memory:db;create=true';
>
> CREATE TABLE brokers
>
> (
>
>    id INT,
>
>    broker_name VARCHAR(50),
>
>    csv_reg_ex VARCHAR(50),
>
>    active BOOLEAN
>
> );
>
> -- ERROR 42X04: Column 'B' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B' is not a column in the target table.
>
> SELECT
>
>      ID,
>
>      BROKER_NAME AS B,
>
>      CSV_REG_EX AS C,
>
>      ACTIVE
>
> FROM
>
>      APP.BROKERS
>
> FOR UPDATE OF
>
>      B,
>
>      C,
>
>      ACTIVE;
>
> -- ERROR 42X42: Correlation name not allowed for column 'BROKER_NAME' because it is part of the FOR UPDATE list.
>
> SELECT
>
>      ID,
>
>      BROKER_NAME AS B,
>
>      CSV_REG_EX AS C,
>
>      ACTIVE
>
> FROM
>
>      APP.BROKERS
>
> FOR UPDATE OF
>
>      broker_name,
>
>      csv_reg_ex,
>
>      ACTIVE;
>
> -- succeeds
>
> SELECT
>
>      ID,
>
>      BROKER_NAME,
>
>      CSV_REG_EX,
>
>      ACTIVE
>
> FROM
>
>      APP.BROKERS
>
> FOR UPDATE OF
>
>      broker_name,
>
>      csv_reg_ex,
>
>      ACTIVE;
>
>