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;
