Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to update the view of mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Some views are not updatable because updates to these views cannot be meaningfully converted to the corresponding base tables.

2. In general, you can update the row and row subset view. In addition to the column subset view, you can theoretically update some views.

Example

-- create view ldq_t1CREATE VIEW ldq_t1 ASSELECT * FROM t3WHERE id1 > 10 WITH CHECK OPTION;-- query all results SELECT * FROM ldq_t1; in ldq_t1-- create view ldq_t2CREATE VIEW ldq_t2 ASSELECT * FROM ldq_t1WHERE id1 < 30 WITH LOCAL CHECK OPTION;-- create view ldq_t3CREATE VIEW ldq_t3 ASSELECT * FROM ldq_t1WHERE id1 < 30 WITH CHECK OPTION -- Update view ldq_t2 (only data that exists in ldq_t2 can be updated) SELECT * FROM ldq_t2;-- View ldq_t2 current record UPDATE ldq_t2 SET id1=5 WHERE id2=22;-- can execute UPDATE ldq_t2 SET id1=35 WHERE id2=22; successfully-- will report an error CHECK OPTION failed (because the id2=22 record will disappear from ldq_t2 after the statement is executed) UPDATE ldq_t2 SET id1=28 WHERE id2=22 -- can execute successfully-- Update ldq_t3SELECT * FROM ldq_t3;UPDATE ldq_t3 SET id1=5 WHERE id2=22;-- will report an error CHECK OPTION failed (because after the data update, you must also ensure that it is still in ldq_t3 and ldq_t1, and the id2=22 record will disappear from ldq_t1 after the statement is executed) UPDATE ldq_t3 SET id1=15 WHERE id2=22;-- can execute UPDATE ldq_t3 SET id1=35 WHERE id2=22 successfully. -- there will be an error CHECK OPTION failed (because the id2=22 record will disappear from the ldq_t3 after executing the statement) DELETE FROM ldq_t3 WHERE id2=22;-- this is the end of the successful execution of "how to update the view by mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.