CODEWITHSUNDEEP
mysqlsql
Miyazaki Ahmad
Miyazaki Ahmad

Reputation: 139

MySQL error 1443 workaround

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

Upvotes: 3

Views: 3936

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 4

Related Questions