2

I am able to create a view for retrieving the records based on some criteria, but does the view only support for retrieving the records or will it support for delete and update the records in the tables.

create view onlinetestview as
    select * from onlinetest where experience = '1-2 Years'

I used the above view its working fine but when i am trying to create a view for updating the table record its giving me an error:

create view onlinetestview2 as
    update  onlinetest set experience='1-2Years' where experience='1-2 Years';

The error is : Lookup Error - MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update onlinetest set experience='1-2Years' where experience='1-2 Years'' at line 1

0

The syntax error (note that it points to "update") is that a VIEW can only be of a SELECT, not an UPDATE.

Once you have created the view on a SELECT, you can treat the VIEW (your onlinetestview) as if it were a table (with some caveats). So... This might work:

update  onlinetestview           -- Note: this is the VIEW name
    set   experience='1-2Years'
    where experience='1-2 Years';
  • its not giving error but its not updating in the table with the set value which we are setting using the view. – zameer Aug 17 '16 at 7:33
  • What version are you using? Does EXPLAIN UPDATE ... work on your version? – Rick James Aug 17 '16 at 15:55
  • MySQL Version is 5.5.15 and i tried with the Explain command but its giving error, I tried with the following query : explain update onlinetestview set experience='1-2Years' where experience='1-2 Years'; – zameer Aug 17 '16 at 16:16
  • OK, EXPLAIN UPDATE was not available until 5.6. – Rick James Aug 17 '16 at 19:33
  • Does the update list number of rows matched an number changed? – Rick James Aug 17 '16 at 19:34
3

Yes, but with caveats. Some aspect of your view probably breaks one or more of these conditions:

http://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. To be more specific, a view is not updatable if it contains any of the following:

  • Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION or UNION ALL
  • Subquery in the select list (fails for INSERT, okay for UPDATE, DELETE)
  • Certain joins (see additional join discussion later in this section)
  • Reference to nonupdatable view in the FROM clause
  • Subquery in the WHERE clause that refers to a table in the FROM clause
  • Refers only to literal values (in this case, there is no underlying table to update)
  • ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)
  • Multiple references to any column of a base table (fails for INSERT, okay for UPDATE, DELETE)

If you post the view definition we might be able to tell you which one

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.