sql - Is it possible to insert data into a MySQL view? -
i made mysql view 4 tables. possible insert data view , have mysql automatically pass data right table?
if using inner joins, , view contains columns in base tables, view might updatable. however, multiple-table updatable view, insert can work if inserts single table. split insert operation multiple insert statements.
you may want check out following article more information on topic:
consider following example:
create table table_a (id int, value int); create table table_b (id int, ta_id int, value int); insert table_a values (1, 10); insert table_a values (2, 20); insert table_a values (3, 30); insert table_b values (1, 1, 100); insert table_b values (2, 1, 200); insert table_b values (3, 2, 300); insert table_b values (4, 2, 400); now let's create view:
create view v select a.id a_id, b.id b_id, b.ta_id, a.value v1, b.value v2 table_a inner join table_b b on (b.ta_id = a.id); select * 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 v (a_id, b_id, ta_id, v1, v2) values (3, 5, 3, 30, 500); -- error 1393 (hy000): can not modify more 1 base table through join view but can split 2 operations:
insert v (a_id, v1) values (3, 30); -- query ok, 1 row affected (0.00 sec) insert v (b_id, ta_id, v2) values (5, 3, 500); -- query ok, 1 row affected (0.00 sec) result:
select * 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)
Comments
Post a Comment