delete a record from two tables in php/mysql

Question

I'm trying to delete a "boat" from boat table and associated qualifications in another table using the following code:

DELETE FROM tbl_boat, tbl_qualifications 
WHERE tbl_boat.BT_ID = '$bt_id' AND tbl_boat.BT_ID = tbl_qualifications.BT_ID;

The problem is I'm receiving following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE tbl_boat.BT_ID = 113 AND tbl_boat.BT_ID = tbl_' at line 2 .

Appreciate any help on this.

may be you can get idea by this one. http://stackoverflow.com/questions/1339851/delete-rows-from-two-tables-in-one-query — Ravichandran Jothi, Sep 13 '12 at 13:14
You sure that this will work? `...WHERE tbl_boat.BT_ID = '$bt_id' AND tbl_boat.BT_ID = tbl_qualifications.BT_ID`: this would return the row only of `$bt_id` and `tbl_qualifications.BT_ID` were identical... — Spontifixus, Sep 26 '12 at 16:04

score 0 · Answer 1 · answered Sep 13 '12 at 13:14

0

You may want to try INNER JOIN.

DELETE FROM tbl_boat
INNER JOIN tbl_qualifications
WHERE tbl_boat.BT_ID = '$bt_id' AND tbl_boat.BT_ID = tbl_qualifications.BT_ID;

I haven't tested this, but I think this will work.

answered Sep 13 '12 at 13:14

ljhljh235

51
1
10

Maybe adding some more information will make this answer more useful. A [link](http://dev.mysql.com/doc/refman/5.0/en/delete.html) to mysql manual is good here, to explain how `DELETE` on multiple tables work – Yaroslav Oct 03 '12 at 06:46

score 0 · Answer 2 · answered Sep 13 '12 at 13:15

0

You have to tell MySQL how the two tables are related:

DELETE tbl_boat, tbl_qualifications
FROM
    tbl_boat
    INNER JOIN tbl_qualifications USING (BT_ID)
WHERE
    tbl_boat.BT_ID = '$bt_id'

answered Sep 13 '12 at 13:15

Sean Bright

118,630
17
138
146

score 0 · Accepted Answer · answered Sep 13 '12 at 13:15

0

You need to perform two delete :

-- DELETE all associations first ...
DELETE FROM tbl_qualifications WHERE BT_ID = '$bt_id';

-- ... then delete the boat
DELETE FROM tbl_boat WHERE BT_ID = '$bt_id';

answered Sep 13 '12 at 13:15

Stephan

41,764
65
238
329

score 0 · Answer 4 · edited May 23 '17 at 12:27

0

it should be

DELETE tbl_boat, tbl_qualifications FROM tbl_boat, tbl_qualifications 
WHERE tbl_boat.BT_ID = '$bt_id' AND tbl_boat.BT_ID = tbl_qualifications.BT_ID;

btw take a look at How to delete from multiple tables in MySQL?

edited May 23 '17 at 12:27

Community

1
1

answered Sep 13 '12 at 13:16

Lesto

2,260
2
19
26

I tried it but this does not work. I receive the same error message. – Mr.Y Sep 14 '12 at 08:29
It only works if I break it into two delete statements and only if delete from qualification executes before boat. – Mr.Y Sep 14 '12 at 08:32

score 0 · Answer 5 · answered Sep 13 '12 at 13:23

0

This will definitely work:

DELETE a.*, b.*
FROM tbl_boat AS a
INNER JOIN tbl_qualifications AS b
    ON b.BT_ID = a.BT_ID
WHERE tbl_boat.BT_ID = '$bt_id';

Also, if the BT_ID is INTEGER type, then remove the quotation from around $bt_id.

answered Sep 13 '12 at 13:23

hjpotter92

78,589
36
144
183

score 0 · Answer 6 · answered Mar 10 '14 at 16:17

0

Try This

DELETE uploadfeat,postfeeds,postcomment FROM uploadfeat INNER JOIN postfeeds INNER JOIN postcomment
WHERE uploadfeat.id=postfeeds.postID AND uploadfeat.id=postcomment.postID AND uploadfeat.id=23

answered Mar 10 '14 at 16:17

Shailesh Singh

317
1
11

delete a record from two tables in php/mysql

6 Answers6

Linked