The updateability of the view is related to the definition of the query in the view

1. Those in MySQL that try to make it non-updatable?The following types of views are not updatable

1. SQL statement containing the following keywords: aggregate function (sum, min, max, count), distinct, group by, having, union or uinon all

2. Constant view

3.select Contains subquery

4.join

5.from a non-updatable attempt

6.The subquery of the where clause refers to the from clause Table

2.UpdateView condition restrictions

##WITH [CASCADED | LOCAL] CHECK OPTION determines the conditions for updating the view.

LOCAL means that as long as the conditions of this view are met, it can be updated

##CASCADED Then all view conditions for this view must be met before it can be updated

If it is not clear whether it is local or cascade, the default iscascade

##In order to facilitate understanding, I will illustrate it through examples

## The known data structure of the t3 table is as follows:

##

-- 创建视图 ldq_t1 CREATE VIEW ldq_t1 AS SELECT * FROM t3 WHERE id1 > 10 WITH CHECK OPTION ; -- 查询ldq_t1中的所有结果 SELECT * FROM ldq_t1; -- 创建视图 ldq_t2 CREATE VIEW ldq_t2 AS SELECT * FROM ldq_t1 WHERE id1 < 30 WITH LOCAL CHECK OPTION ; -- 创建视图 ldq_t3 CREATE VIEW ldq_t3 AS SELECT * FROM ldq_t1 WHERE id1 < 30 WITH CHECK OPTION ; -- 更新视图ldq_t2（只有ldq_t2中存在的数据都可以更新） SELECT * FROM ldq_t2; -- 查看ldq_t2当前记录 UPDATE ldq_t2 SET id1=5 WHERE id2=22; -- 可以执行成功 UPDATE ldq_t2 SET id1=35 WHERE id2=22; -- 将会报错CHECK OPTION failed(因为执行该语句之后，id2=22记录将从ldq_t2消失) UPDATE ldq_t2 SET id1=28 WHERE id2=22; -- 可以执行成功 -- 更新ldq_t3 SELECT * FROM ldq_t3; UPDATE ldq_t3 SET id1=5 WHERE id2=22; -- 将会报错CHECK OPTION failed（因为数据更新之后，必须还要保证其仍然在ldq_t3和ldq_t1之中, 该语句执行后id2=22记录将从ldq_t1消失） UPDATE ldq_t3 SET id1=15 WHERE id2=22; -- 能够执行成功 UPDATE ldq_t3 SET id1=35 WHERE id2=22; -- 将会报错CHECK OPTION failed（因为执行该语句之后，id2=22记录将从ldq_t3消失） DELETE FROM ldq_t3 WHERE id2=22; -- 执行成功

The above is the detailed content of view update in mysql. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!