Single SQL Query to Delete records in two tables

Question

These are my tables:

tbl_student (studID,Firstname)

My studid is priid

finger_template(Detail_ID,FingerprintMask)

I want to delete the record in tbl_student and finger_template. Here's my Query:

command = New MySqlCommand("DELETE from tbl_student where studid='" & priid & "'", connection, transaction)

Hope someone will help

How are these 2 tables connected? Because if they are not connected, then you cannot delete from both of them using a single delete command, you need 2. Although, you can issue both delete commands in a single call. — Shadow, Feb 15 '16 at 09:18
If the tables has link together this is quite easy, are they connected to each other (tables)? -- By [MattMagallo](http://stackoverflow.com/users/4061583/matt-magallo) — Bhargav Rao, Feb 15 '16 at 09:26
@MykeeBrylleSepulveda if they are connected in mysql(innoDB) you could just remove the PrimaryKey and all the ForeignKey will be removed -- By [MattMagallo](http://stackoverflow.com/users/4061583/matt-magallo) — Bhargav Rao, Feb 15 '16 at 09:33
My recommendation is the innodb engine and cascading deletes for those with concern over data and referential integrity — Drew, Feb 17 '16 at 01:23

Matt Magallo · Answer 1 · 2016-02-15T09:46:44.287

1

Table: tbl_student

+-------------+-----------+
| studID (PK) | Firstname |
+-------------+-----------+
| **000001**  | John      |
+-------------+-----------+

Table: finger_template

+----------------+-----------------+-------------+
| Detail_ID (PK) | FingerprintMask | studID (FK) |
+----------------+-----------------+-------------+
| 000001         | SampleMask      | **000001**  |
+----------------+-----------------+-------------+

If the tbl_student studID PK is link to finger_template stuID FK you could just simply remove the studID PK and the FK will be remove as well DELETE FROM tbl_student WHERE studID=000001

edited Feb 15 '16 at 09:46

answered Feb 15 '16 at 09:20

Matt Magallo

328
1
20

1

This is really a comment, not an answer. With a bit more rep, [you will be able to post comments](//stackoverflow.com/privileges/comment). – Bhargav Rao Feb 15 '16 at 09:22
sorry @BhargavRao i can't comment yet because my rep is still not enough..:( – Matt Magallo Feb 15 '16 at 09:25
I have added it as a comment, you can delete your ans if you feel like. Regards – Bhargav Rao Feb 15 '16 at 09:26
@MattMagallo Then don't answer a comment! `:)` – Praveen Kumar Purushothaman Feb 15 '16 at 09:30
One last comment sir @BhargavRao, To: @mykee if they are connected in mysql(innoDB) you could just remove the `PrimaryKey` and all the `ForeignKey` will be removed – Matt Magallo Feb 15 '16 at 09:32
done, (Hope it is the last :P ) – Bhargav Rao Feb 15 '16 at 09:33
@PraveenKumar, sorry i ddnt mean to my rep is still not enough so im force to do so, my appologies guys – Matt Magallo Feb 15 '16 at 09:33
@MattMagallo No worries. I guess it is better to delete this answer at this point, and if you get the answer, edit it and then undelete it. – Praveen Kumar Purushothaman Feb 15 '16 at 09:34

Single SQL Query to Delete records in two tables

1 Answers1