mysql错误码0449,MySQL错误1443解决方法

I believe my question can be referred to another question with almost the same title, but the answer was unfortunately not clear enough for me and that question is 6 years old too, maybe there are already some changes made to MySQL.

I wanted to delete a row(s) from a table without even updating/inserting/deleting rows from the view tables, from what I know, mysql unfortunately prevents us from making changes to a table that is referencing to a view table.

I need to reference to the table which brings me to option 2 and my question:

How can I "dump the list to a temporary table and use that for your subquery." or is there a workaround to make this code work?

I am using MySQL version 5.6.12 and

the code I'm working on:

DELETE FROM student

WHERE (SUBSTR(student.stud_no,1,4) = 1234)

AND NOT EXISTS

(SELECT vr.stud_no FROM viewroom vr WHERE

vr.stud_no = student.stud_no)

AND NOT EXISTS

(SELECT vlnr.stud_no,vlnr.status FROM viewlateststudentnr

vlnr WHERE (student.stud_no = vlnr.stud_no) AND (vlnr.status = 'confirmed') )

And the error :

1443 - The definition of table 'vr' prevents operation DELETE on table 'student'.

解决方案

Apparently, your views must include the table you want to delete from. This suggests outer joins:

DELETE s

FROM student s LEFT JOIN

viewroom vr

ON vr.stud_no = s.stud_no LEFT JOIN

viewlateststudentnr vlnr

ON s.stud_no = vlnr.stud_no AND vlnr.status = 'confirmed'

WHERE SUBSTR(s.stud_no, 1, 4) = 1234 AND

vr.stud_no IS NULL AND vlnr.stud_no IS NULL;