Deleting data using a view that has multiple tables

To see if you can delete data via a view that has multiple tables, you need to set up a copy of an existing table so that you don't delete the data in the original table. You can do that with the following query: 

USE lahmansbaseballdb;
DROP TABLE IF EXISTS collegeplaying_copy;
CREATE TABLE collegeplaying_copy
SELECT * FROM collegeplaying;

Next, you can create a view with this new table in it with the following query: 

USE lahmansbaseballdb;
DROP VIEW IF EXISTS collegeplayingbyname;
CREATE VIEW collegeplayingbyname AS
SELECT namefirst, namelast, schoolid, yearid
FROM collegeplaying_copy c
INNER JOIN people p
ON p.playerid = c.playerid;

Let's delete a value using the view we just created with the following query: 

USE lahmansbaseballdb;
DELETE FROM collegeplayingbyname
WHERE (namefirst = 'David' AND namelast = 'Aardsma')
AND (schoolid = 'rice' AND yearid = 2003);

You will receive an error: 

Error Code: 1395. Can not delete from join view 'lahmansbaseballdb.collegeplayingbyname'

This shows that you can't delete from a view that includes multiple tables. 

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
13.58.113.193