Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Does deleting row from view delete row from base table in MySQL?
Yes, deleting row from view delete row from base table. Let us understand this by creating a new table. The query to create a table is as follows
mysql> create table deleteFromBaseTableDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.83 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into deleteFromBaseTableDemo(Name) values('John');
Query OK, 1 row affected (0.18 sec)
mysql> insert into deleteFromBaseTableDemo(Name) values('Carol');
Query OK, 1 row affected (0.15 sec)
mysql> insert into deleteFromBaseTableDemo(Name) values('Bob');
Query OK, 1 row affected (0.14 sec)
mysql> insert into deleteFromBaseTableDemo(Name) values('Sam');
Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement. The query is as follows −
mysql> select *from deleteFromBaseTableDemo;
The following is the output
+----+-------+ | Id | Name | +----+-------+ | 1 | John | | 2 | Carol | | 3 | Bob | | 4 | Sam | +----+-------+ 4 rows in set (0.00 sec)
Let us create a view. The query to create a view is as follows
mysql> create view delete_view as select Id,Name from deleteFromBaseTableDemo; Query OK, 0 rows affected (0.17 sec)
Let us check all the records of view. The query is as follows −
mysql> select *from delete_view;
The following is the output
+----+-------+ | Id | Name | +----+-------+ | 1 | John | | 2 | Carol | | 3 | Bob | | 4 | Sam | +----+-------+ 4 rows in set (0.05 sec)
Now if you delete from view then it will automatically delete from base table as well.
The query to delete row from view is as follows
mysql> delete from delete_view where Id=4; Query OK, 1 row affected (0.26 sec)
Let us check all the records from view and parent table. The query is as follows −
mysql> select *from delete_view;
The following is the output
+----+-------+ | Id | Name | +----+-------+ | 1 | John | | 2 | Carol | | 3 | Bob | +----+-------+ 3 rows in set (0.00 sec)
The query to show all records from base table is as follows
mysql> select *from deleteFromBaseTableDemo;
The following is the output
+----+-------+ | Id | Name | +----+-------+ | 1 | John | | 2 | Carol | | 3 | Bob | +----+-------+ 3 rows in set (0.00 sec)
Look at both the sample outputs, Id with value 4 has been deleted from the parent table as well as from view.
1K+ Views
