Can't update column in sql table

Question

I created a view

CREATE VIEW `distance` AS
    SELECT 
        `rv`.`id` AS `id`,
        `rv`.`TIMESTAMP` AS `TIMESTAMP`,
        `rv`.`name` AS `name`,
        `rr`.`distance` AS `distance`,
        `false` AS `new_name`
    FROM
        (`recognition_view` `rv`
        JOIN `raw_recognition` `rr` ON ((`rv`.`rr_id` = `rr`.`id`)))

And i need to update new_name column, but i get a response Error Column 'new_name' is not updatable. Any way to do it?

What do you mean "update `new_name`"? It is a constant, not a column in the table, so you cannot update it. — Gordon Linoff, Jan 23 '19 at 11:37
Are you sure you're working with Db2? Backticks look suspect. — mustaccio, Jan 23 '19 at 13:29

score 1 · Answer 1 · answered Jan 23 '19 at 11:29

1

More than 1 table in the FROM clause. This view can't be updatable. Read about Updatable views. You can create an instead of trigger to achieve your goal.

answered Jan 23 '19 at 11:29

Mark Barinstein

11,456
2
8
16

Jeroen · Answer 2 · 2019-01-23T11:34:10.333

The new_name is not part of your table(s). It is constructed in your view and always set to false. Question yourself this: What would the result of an update on this column be? Where would it be persisted?

If you want to be able to update this value, you should consider adding it to one of your other tables, or create a new table and join it in the existing view.

You could store the new_name information in your recognition_view or raw_recognition table. For simplicity sake, I will explain how to do the latter:

CREATE TABLE raw_recognition (
    ...
    new_name NUMBER(1)
)

and then in your CREATE VIEW change the

`false` AS `new_name`

to

`rr.new_name` AS `new_name`

I update this column in my front-end. And sometimes i need to set `true` to `new_name`. — J. Doe, Jan 23 '19 at 11:18

Can't update column in sql table

2 Answers2