db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Martin Neumann <martin.neum...@nsyss.com>
Subject Re: Question regarding FOR UPDATE
Date Tue, 23 Jul 2019 19:21:57 GMT
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;
> 
> 

Mime
View raw message