how to delete from more than two (2) tables using mysql joins

Question

I'm trying to run a multi delete on a my database but when it runs it runs with no errors but nothing was deleted? is it a case that an inner join will fail if even one of the table doesn't have anything to link to another and on?

using the query in an Express.js api I'm trying to finish us.

I usual test big queries like this in DBMS before i place them in my api.

below is the query:

DELETE task,issue,milestone,help_source,project_task,project_source,project_milestone,task_created_by,mileStone_created_by,issue_created_by,source_created_by,task_issue,milestone_task,project_creation,project
      from project_creation
      INNER JOIN project_milestone ON project_milestone.projectId = project_creation.projectId
      INNER JOIN project_task ON project_task.projectId = project_creation.projectId
      INNER JOIN project_source ON project_source.projectId = project_creation.projectId
      INNER JOIN task_created_by ON task_created_by.taskId = project_task.taskId
      INNER JOIN mileStone_created_by On mileStone_created_by.mileStoneId = project_milestone.mileStoneId
      INNER JOIN source_created_by ON source_created_by.sourceId = project_source.sourceId
      INNER JOIN task_issue ON task_issue.taskId = project_task.taskId
      INNER JOIN issue_created_by ON issue_created_by.issueId = task_issue.issueId
      INNER JOIN milestone_task On milestone_task.taskId = project_task.taskId
      INNER JOIN task On task.taskId = project_task.taskId
      INNER JOIN issue ON issue.issueId = task_issue.issueId
      INNER JOIN milestone ON milestone.mileStoneId = project_milestone.mileStoneId
      INNER JOIN help_source On help_source.sourceId = project_source.sourceId
      INNER JOIN project ON project.projectId = project_creation.projectId
    WHERE project.projectId = 59;

is there some error in my logic? and if yes what is it? or is it deleting without me knowing?

Questions you may ask:

Did i try searching stack overflow? ANS: yes i did but can't seem to find an answer that fits this weird situation

so there is no error? ANS: no there is no errors when i run the query

Replace `DELETE ... FROM` with `SELECT * FROM`, to see if you are seeing what you expect to delete. — Amadan, Jan 20 '19 at 02:35
Sounds like you might want/need to learn about InnoDB's [foreiyn keys](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html) and cascade delete. [Here](http://www.mysqltutorial.org/mysql-on-delete-cascade/) is a pretty good tutorial. — Raymond Nijland, Jan 20 '19 at 02:38
oh wow that was a nice check. anyways yes when i change it to `select * from` it does show all the columns but nothing is retrieved. one or more of the tables may be blank already will this stop the process? — delavago1999, Jan 20 '19 at 02:39
@RaymondNijland i did use foreign Key tho and most of the tables above are junction tables to make sure that database was at least in the forth normal form (4NF) — delavago1999, Jan 20 '19 at 02:43
normal form (1-4NF) is old stuff i like using [FCO-IM](https://en.wikipedia.org/wiki/FCO-IM) much more. — Raymond Nijland, Jan 20 '19 at 02:48
@RaymondNijland: FCO-IM is a method of modeling an information system. Normal forms are formal descriptions of specific properties of the model. They are different things. In fact, the FCO-IM paper describes its schemata in terms of normal forms all over. It's almost as ridiculous as "Spices (salt, pepper, saffron...) are old stuff. I like using [induction cooking](https://en.wikipedia.org/wiki/Induction_cooking) much more." — Amadan, Jan 22 '19 at 00:20
Well @Amadan i think you misunderstood "old" because of the short comment. i find normal form old call it a "luxury" problem... Like you said FCO-IM deals with user cases off a information systems and or domain information of anny kind which may describe participation papers.. Normal form also looks into those domain information so there are not that different in that way .. FCO-IM adds a extra layer on top of it, just like C/C++ does on top on assembly i also would call assembly old stuff but still you use ("unknowingly" or better said indirect) it when compiling C/C++ applications. — Raymond Nijland, Jan 22 '19 at 00:57

score 0 · Accepted Answer · answered Jan 20 '19 at 02:55

0

try to look on that topic : How to Delete using INNER JOIN with SQL Server?

As far as I could see you need to specify aliases for each table. So task, issue... are aliases but cannot see any of then after inner-join table name (true, same names as tables but maybe alias are required) Eg: INNER JOIN task task ON... (now task is an alias, so you delete from table task) Didn't check it, but if is to be work that should be the fix.

answered Jan 20 '19 at 02:55

Traian GEICU

1,750
3
14
26

gonna try this now – delavago1999 Jan 20 '19 at 02:59
the results are the same. but thanks. i think i might just have to split this big query into smaller ones – delavago1999 Jan 20 '19 at 03:11
hope commit is on – Traian GEICU Jan 20 '19 at 03:13
commit? whats that? – delavago1999 Jan 20 '19 at 03:22
when insert, update, delete if commit is off then no changes are made into tables (just virtual). Basically it said to proceed with the command immediately.Could try with 1 simple insert to see if something is added and if is then commit is on – Traian GEICU Jan 20 '19 at 03:26
On initial problem if constrains (foreign key are on tables then multi delete with master-slave tables could not work). Eg. on delete restrict. So first you should delete records from slave tables and after from master (database integrity). A solution for delete from master first is with a trigger ( the trigger will proceed with delete from slave first and then record from master could be deleted) – Traian GEICU Jan 20 '19 at 03:30

how to delete from more than two (2) tables using mysql joins

1 Answers1