-2

I want the query which helps me to delete a record on one click Actually data come from two different table and I join both using inner join and display on the table now I create a button for delete and want a query which deletes that complete row on click.

Someone told me we apply two query at the same time I try too much but can't understand how. I don't have deep knowledge about Database I'm new.

Awais Butt
  • 15
  • 6

3 Answers3

0

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Mukesh Dhisale
  • 1
  • 2
0

You should set relationship with cascade delete between these two tables. Once you have done that, you just need to delete master table entry, rest of the work will be done automatically. You don't need to run two query for delete.

Virendra Jadeja
  • 821
  • 1
  • 10
  • 20
0

I have 2 sample tables here:

MariaDB [Test_delete_query]> select * from tbl1;
+----+--------------+
| id | student_name |
+----+--------------+
|  1 | Tom          |
|  2 | Emily        |
+----+--------------+
2 rows in set (0.00 sec)

MariaDB [Test_delete_query]> select * from tbl2;
+----+------------+-------------+
| id | student_id | course_name |
+----+------------+-------------+
|  1 |          1 | Mathematic  |
|  2 |          1 | History     |
+----+------------+-------------+
2 rows in set (0.00 sec)

It is possible to delete Tom with his courses using the following SQL query:

delete tbl1.*, tbl2.* from tbl1 join tbl2 on ( tbl1.id = tbl2.student_id ) where tbl1.id = 1;

But as @mukesh-dhisale said you should set relationship with cascade delete between the two tables so when you delete the primary record its children will be deleted automatically from other tables. @marc-b thought us how to setup our tables to do so here.

mahyard
  • 1,230
  • 1
  • 13
  • 34
  • I know but my problem is i haven't well knowledge about database relationship. would you please guide me? – Awais Butt Jun 12 '17 at 10:03
  • @AwaisButt Look at [this post](https://stackoverflow.com/questions/2914936). You'll find useful tips there. If you have another question don't hesitate and ask again. – mahyard Jun 12 '17 at 11:52
  • here is the error when i run this Query... "Unknown table 'tab_issueid' in MULTI DELETE" – Awais Butt Jun 13 '17 at 10:44
  • @AwaisButt As the message said MySQL can't find tab_issueid. I think you have a misspelled table name. Isn't it tab_issued? – mahyard Jun 13 '17 at 14:59
  • Dear here is my query i write, would you please check what is miss.. "delete tab_issue.tab_issueID, tab_return.tab_returnID from tab_issue join tab_return on ( tab_return.tab_returnID = tab_issue.tab_issueID ) where tab_issue.tab_issueID = 26;" – Awais Butt Jun 14 '17 at 06:09
  • @AwaisButt check if it works: "delete tab_issue.*, tab_return.* from tab_issue join tab_return on ( tab_return.tab_returnID = tab_issue.tab_issueID ) where tab_issue.tab_issueID = 26;" – mahyard Jun 14 '17 at 06:25