CODEWITHSUNDEEP
sqldb2sql-updateupdatable-views
J. Doe
J. Doe

Reputation: 1

Can't update column in sql table

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?

Upvotes: 0

Views: 649

Answers (2)

Jeroen
Jeroen

Reputation: 44

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`

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12339

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.

Upvotes: 1

Related Questions