CREATE SCHEMA IF NOT EXISTS testing;
USE testing;

CREATE OR REPLACE TABLE testing.table1 (
	`commonId` int(11) NOT NULL,
	`column1notnull` varchar(20) NOT NULL,
	PRIMARY KEY (`commonId`)
) ENGINE=InnoDB;

CREATE OR REPLACE TABLE testing.table2 (
	`commonId` int(11) NOT NULL,
	`column2notnull` varchar(20) NOT NULL,
	PRIMARY KEY (`commonId`)
) ENGINE=InnoDB;

CREATE OR REPLACE TABLE testing.table3 (
	`commonId` int(11) NOT NULL,
	`column3notnull` varchar(20) NOT NULL,
	PRIMARY KEY (`commonId`)
) ENGINE=InnoDB;

CREATE OR REPLACE VIEW testing.tableview
AS
SELECT t1.commonId,
	t1.column1notnull as t1c1,
	t2.column2notnull as t2c1,
	t3.column3notnull as t3c1
FROM testing.table1 t1
	LEFT JOIN testing.table2 t2 ON t1.commonId = t2.commonId
	LEFT JOIN testing.table3 t3 ON t1.commonId = t3.commonId;

INSERT INTO testing.table1 (commonId, column1notnull) VALUES (1, '1-1');
INSERT INTO testing.table2 (commonId, column2notnull) VALUES (1, '1-2');
-- INSERT INTO testing.table3 (commonId, column3notnull) VALUES (1, '1-3');

SELECT * FROM testing.tableView;

UPDATE testing.tableView set t1c1 = '1-1-a' WHERE commonId = 1; -- 1 row updated
UPDATE testing.tableView set t2c1 = '1-2-a' WHERE commonId = 1; -- 1 row updated
UPDATE testing.tableView set t3c1 = '1-3-a' WHERE commonId = 1; -- 0 row updated

SELECT * FROM testing.tableView;

-- Can not modify more than one base table through a join view 'testing.tableview'
UPDATE testing.tableView set t1c1 = '1-1-b', t2c1 = '1-2-b', t3c1 = '1-3-b' WHERE commonId = 1;

-- The target table tableView of the INSERT is not insertable-into
INSERT INTO testing.tableView (commonId, t1c1, t2c1, t3c1) VALUES (2, '2-1', '2-2', '2-3');

-- The target table tableView of the INSERT is not insertable-into
INSERT INTO testing.tableView (commonId, t1c1) VALUES (2, '2-1');