MySQL错误1443的解决方法 [英] MySQL error 1443 workaround

问题描述

我相信我的问题可以参考另一个问题，相同的标题，但不幸的是，答案对我来说还不够清楚，而且这个问题也已有6年历史了，也许对MySQL进行了一些更改.

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.

我想从表中删除行，甚至不从视图表中更新/插入/删除行，据我所知，不幸的是，mysql阻止了我们对引用视图表的表进行更改. 我需要引用该表，该表将我带到了选项2和我的问题:

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:

如何"将列表转储到临时表中并将其用于子查询".或是否有一种解决方法可以使此代码正常工作?

我正在使用MySQL版本5.6.12和 我正在处理的代码:

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') )

错误:

1443-表'vr'的定义阻止了对表'student'的删除操作.

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;

这篇关于MySQL错误1443的解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！