Is it possible to insert data into a MySQL view?

Question

I made a MySQL view with 4 tables. Is it possible to insert data into the view and have MySQL automatically pass the data into the right table?

Answer 1

If you are using inner joins, and your view contains all the columns in the base tables, then your view might be updatable. However, for a multiple-table updatable view, INSERT can work if it inserts into a single table. You could split your insert operation into multiple INSERT statements.

You may want to check out the following article for more information on the topic:

• MySQL Reference Manual :: Updatable and Insertable Views

Consider the following example:

CREATE TABLE table_a (id int, value int); CREATE TABLE table_b (id int, ta_id int, value int);  INSERT INTO table_a VALUES (1, 10); INSERT INTO table_a VALUES (2, 20); INSERT INTO table_a VALUES (3, 30);  INSERT INTO table_b VALUES (1, 1, 100); INSERT INTO table_b VALUES (2, 1, 200); INSERT INTO table_b VALUES (3, 2, 300); INSERT INTO table_b VALUES (4, 2, 400); 

Now let's create a view:

CREATE VIEW v AS      SELECT      a.id a_id, b.id b_id, b.ta_id, a.value v1, b.value v2     FROM        table_a a     INNER JOIN  table_b b ON (b.ta_id = a.id);  SELECT * FROM v; +------+------+-------+------+------+ | a_id | b_id | ta_id | v1   | v2   | +------+------+-------+------+------+ |    1 |    1 |     1 |   10 |  100 | |    1 |    2 |     1 |   10 |  200 | |    2 |    3 |     2 |   20 |  300 | |    2 |    4 |     2 |   20 |  400 | +------+------+-------+------+------+ 4 rows in set (0.00 sec) 

The following INSERT fails:

INSERT INTO v (a_id, b_id, ta_id, v1, v2) VALUES (3, 5, 3, 30, 500); -- ERROR 1393 (HY000): Can not modify more than one base table through a join view  

But we can split it into two operations:

INSERT INTO v (a_id, v1) VALUES (3, 30); -- Query OK, 1 row affected (0.00 sec) INSERT INTO v (b_id, ta_id, v2) VALUES (5, 3, 500); -- Query OK, 1 row affected (0.00 sec) 

Result:

SELECT * FROM v; +------+------+-------+------+------+ | a_id | b_id | ta_id | v1   | v2   | +------+------+-------+------+------+ |    1 |    1 |     1 |   10 |  100 | |    1 |    2 |     1 |   10 |  200 | |    2 |    3 |     2 |   20 |  300 | |    2 |    4 |     2 |   20 |  400 | |    3 |    5 |     3 |   30 |  500 | +------+------+-------+------+------+ 6 rows in set (0.00 sec)