我正试图使用以下代码从boat表中删除一个"boat"以及另一个表中的相关资格:
DELETE FROM tbl_boat, tbl_qualifications
WHERE tbl_boat.BT_ID = '$bt_id' AND tbl_boat.BT_ID = tbl_qualifications.BT_ID;
问题是我收到以下错误:
1064-您的SQL语法出现错误;在第2行的
'WHERE tbl_boat.BT_ID = 113 AND tbl_boat.BT_ID = tbl_
'附近查看与MySQL服务器版本对应的手册,以获得要使用的正确语法。
感谢在这方面的任何帮助。
您需要执行两个删除:
--首先删除所有关联
从tbl_qualifications中删除,其中BT_ID='$BT_ID';--。。。然后删除该船
从tbl_boat删除,其中BT_ID='$BT_ID';
您可能想要尝试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;
我还没有对此进行测试,但我认为这会奏效。
您必须告诉MySQL这两个表是如何关联的:
DELETE tbl_boat, tbl_qualifications
FROM
tbl_boat
INNER JOIN tbl_qualifications USING (BT_ID)
WHERE
tbl_boat.BT_ID = '$bt_id'
它应该是
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;
顺便问一下如何在MySQL中从多个表中删除?
这肯定会起作用:
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';
此外,如果BT_ID
是INTEGER
类型,则从$bt_id
周围移除引号。
试试这个
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