4 Replies - 350 Views - Last Post: 06 July 2016 - 09:08 AM

#1 fearfulsc2   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 179
  • Joined: 25-May 16

Delete from a table using value from a different table?

Posted 06 July 2016 - 08:30 AM

I don't know how to properly word this, but essentially what I am trying to do is delete rows from a table where an id is a certain value. However, that id does not exist on that table but is joined from another table so that you can view it when you select. The code goes like this for it.

 if(mysqli_stmt_prepare($stmt, "INSERT INTO CheckListTasks (TicketID, CheckListID)
    SELECT '$TickID', CheckListID FROM TicketO WHERE TicketType = ? ORDER BY CreatedDate asc"))



So I am using another table to insert values into it, but something came across my mind where if someone wants to delete a certain ID from the other table, is it possible to have that DELETE cascade to the other table? I don't know how to explain it properly, but I'll try to use an example as best as I can to explain it better if possible.

User creates a number and that number has an ID. Based on the type it is, it will INSERT values into the other table based on its type.

So user creates number 2345 and it has an ID that starts from 1. It is of type X;

When they create it, the other table is then populated with
1
2
3
4
based off of its type.

User creates another number 5432 and now it has an ID of 2 and it is type Y;

The table is now populated with:
4
3
2
1

Of course each row will have its corresponding Column ID, but that is just my example.

What I want to be able to do is if the user wants to DELETE number 2345, the other table then has its values deleted as well so that memory space is not being used when it could be reallocated elsewhere.

I'm sorry for explaining this so badly, but that is the best way I can explain it without having any visuals.

Is This A Good Question/Topic? 0
  • +

Replies To: Delete from a table using value from a different table?

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14096
  • View blog
  • Posts: 56,492
  • Joined: 12-June 08

Re: Delete from a table using value from a different table?

Posted 06 July 2016 - 08:36 AM

If I am following you right, yes - you can use joins in a DELETE statement.
http://dev.mysql.com.../en/delete.html
Was This Post Helpful? 0
  • +
  • -

#3 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4202
  • View blog
  • Posts: 13,275
  • Joined: 08-June 10

Re: Delete from a table using value from a different table?

Posted 06 July 2016 - 08:40 AM

Quote

is it possible to have that DELETE cascade to the other table?

sure, that's one option (ON DELETE) for your foreign key constraint.

-- straight from the manual
 [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION] -- action to run when the key is deleted
      [ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION]



minimum code for your case:

FOREIGN KEY (CheckListID) REFERENCES TicketO (CheckListID) ON DELETE CASCADE

Was This Post Helpful? 0
  • +
  • -

#4 fearfulsc2   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 179
  • Joined: 25-May 16

Re: Delete from a table using value from a different table?

Posted 06 July 2016 - 08:46 AM

So, I can use a join to do a DELETE? I did not know that. This is how I select and view the table and the values it has
if(mysqli_stmt_prepare($stmt, "SELECT C.ColumnID, CI.Description, C.CheckListID, C.Status, C.UserID, C.CompletedDate, C.Results, C.CheckListNotes, CI.WebLink FROM CheckListTasks C INNER JOIN Tickets T on C.TicketID=T.TicketID
INNER JOIN CheckListItems CI on C.CheckListID=CI.CheckListID WHERE T.TicketNumber = ? ORDER BY C.ColumnID"))



However, I think TicketID is a foreign key. I will have to see. If it is, then I would only have to add a CASCADE on a DELETE, right?

@Dormilich, but wouldn't that delete the same thing on the other rows if they are of the same type or have the same thing for other types as well? Or would it do it for that one specific thing?
Was This Post Helpful? 0
  • +
  • -

#5 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4202
  • View blog
  • Posts: 13,275
  • Joined: 08-June 10

Re: Delete from a table using value from a different table?

Posted 06 July 2016 - 09:08 AM

Quote

I think TicketID is a foreign key

correct, and the other one is CheckListID.

Quote

but wouldn't that delete the same thing on the other rows if they are of the same type or have the same thing for other types as well?

that only concerns the constraint itself. how you delete the PK (which is referenced in the FK) does not matter for the cascade to happen. if you delete 100 PKs by deleting via their type, then 100 cascades will happen.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1