在一个查询中从两个表中删除

2021-11-20 00:00:00 sql mysql

我在 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 .

推荐答案

不能用分号隔开吗?

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

或

只需使用 INNER JOIN 如下

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

相关文章