Does deleting row from view delete row from base table in MySQL?

MySQLMySQLi Database

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.

raja
Published on 01-Apr-2019 09:37:51
Advertisements