How to Insert Data into a MySQL View

how can i insert into this view mysql

Your insertion has to include all NOT NULL fields

Possible solutions:

1. Change insertion query by adding all required fields
2. Modify table structure and allow nullable

Problematic fields:

1. contactLastName
2. contactFirstName
3. phone

All those fields are not null by definition and you did not provide any value on insertion.

CREATE TABLE customers ( 
  customerNumber int(11) NOT NULL, 
  customerName varchar(50) NOT NULL, 
  contactLastName varchar(50) NOT NULL, 
  contactFirstName varchar(50) NOT NULL, 
  phone varchar(50) NOT NULL, 
  salesRepEmployeeNumber int(11),
  city varchar(50) NOT NULL, 
  country varchar(50) NOT NULL, 
  PRIMARY KEY (customerNumber), 
  KEY salesRepEmployeeNumber (salesRepEmployeeNumber), 
  CONSTRAINT customers_ibfk_1 
  FOREIGN KEY (salesRepEmployeeNumber) 
  REFERENCES employees (employeeNumber) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create or replace view miniltd_customer_view as 
select customerNumber as custno , 
customerName as custname , 
city as custcity , 
country as custcountry 
from customers where customerName like '%Ltd.';

insert into miniltd_customer_view (custno , custname , custcity , custcountry) values (9000 , 'SUNISA Ltd.' , 'Texas' , 'USA');

Live example

http://sqlfiddle.com/#!9/a42221

MySQL - insert / update / delete in views

A view can be updatable and insertable if it follows certain guidelines.

More information here

The short version is: You can't update a view that has any kind of GROUP BY, DISTINCT or Aggregate functions. There are more restrictions, but the manual is you best bet for up to date information on those.

How do inserts into mysql views?

You can't. It's not (always) possible to determine which values would need to be inserted. For example, your view doesn´t have the post column, which might be required, which would make it impossible to insert through a view.

As such, VIEWS are for viewing content only; insertions have to be made on the actual database tables.

Inserting data into multiple tables through an sql view

The MySQL Reference Manual says this about updatable views:

Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. 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.

The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true. The WITH CHECK OPTION clause was implemented in MySQL 5.0.2.

You can find the entire article here.

---

Related Topics