Re: insert into view derived from table with discriminator

[Matteo Tassinari]

Basically here is an example of what I mean:

create table base_table (
a int unsigned auto_increment primary key,
b char(10) default null,
c char(10) default null,
x tinyint unsigned default null
);

create or replace view base_view_1 as
select a, b, c
from base_table
where x = 1;

create or replace view base_view_2 as
select a, b, c
from base_table
where x = 2;

[ ... and so on ... ]

insert into base_view_1 (a, b, c) VALUES (null, 'xxx', 'xxx');

select * from base_view_1; -- no result

select * from base_table; -- one row like (1, 'xxx', 'xxx', null)

See that, in the example, the row from base_table as null as the value for x? Should mysql be able to populate that field automatically based on view definition?

What I would like to achive is that, when I insert on the view base_view_1, since it is defined for x = 1, mysql sets automatically the correct value in base_table, and the same when inserting in base_view_2 where x should be 2, and so on, for example:

insert into base_view_1 (b, c) values ('xxx', 'xxx'); // should insert into base_table the values ('xxx', 'xxx', 1)

insert into base_view_2 (b, c) values ('xxx', 'xxx'); // should insert into base_table the values ('xxx', 'xxx', 2)

// and so on for each distinct discriminator value

Now, is mysql capable of doing this automatically for me, or do I need to resort inserting directly on base_table?