Concat function on MySQL view

Question

For a table with fields 'fname' and 'lname', I created a view with a field 'name' which was a concat of 'fname' and 'lname'.

I want to insert into this view such that the string 'firstname lastname' is inserted into field 'name' of this view and automatically updated as 'firstname' and 'lastname' in fields 'fname' and 'lname' of the parent table.

 create view emp_view as 
    -> select id,concat(fname,' ', lname) as name, email
    -> from employee;

 insert into emp_view
    -> values(1911,'xyz abc','[email protected]');

ERROR 1471 (HY000): The target table emp_view of the INSERT is not insertable-into

But the error says that the view is not insertable. what am i doing wrong?

Answer 1

To quote the documentation on view updatability:

The view columns must be simple column references. They must not be expressions...

So, in your example, the concat makes it a non-updatable view.

This makes sense, if you pass a string to "name", how could MySQL ever work out how to assign the string to the fields "fname" and "lname"?

Answer 2

You cannot insert data on view. A view is a pure SELECT. You have to insert the data into the table that the VIEW is using.

INSERT INTO employee (Id,fname,lname,Email) VALUES(1911,'Firstname','LastName','[email protected]');

The you dislay the data like this.

SELECT * FROM emp_view;

The VIEW will simply run the syntax

SELECT id,concat(fname,' ', lname) as name,email FROM employee;