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?

Answer 1

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`

Answer 2

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.