在一个查询中从两个表中删除 [英] Delete from two tables in one query

问题描述

我在MySQL中有两个表

I have two tables in MySQL

#messages table  : 
messageid
messagetitle 
.
.

#usersmessages table 
usersmessageid 
messageid
userid
.
.

现在，如果我想从邮件表中删除，就可以了.但是，当我通过messageid删除消息时，该记录仍然存在于usersmessage上，因此我必须立即从这两个表中删除.

Now if I want to delete from messages table it's ok. But when I delete message by messageid the record still exists on usersmessage and I have to delete from this two tables at once.

我使用了以下查询:

DELETE FROM messages LEFT JOIN usersmessages USING(messageid) WHERE messageid='1' ; 

然后我测试

   DELETE FROM messages , usersmessages 
   WHERE messages.messageid = usersmessages.messageid 
   and messageid='1' ; 

但是这两个查询没有完成此任务.

But these two queries are not accomplishing this task .

推荐答案

难道不能仅用分号将它们分开吗?

Can't you just separate them by a semicolon?

Delete from messages where messageid = '1';
Delete from usersmessages where messageid = '1'

OR

只需按以下方式使用INNER JOIN

DELETE messages , usersmessages  FROM messages  INNER JOIN usersmessages  
WHERE messages.messageid= usersmessages.messageid and messages.messageid = '1'

这篇关于在一个查询中从两个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！