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

我在mysql中有两个表

#messages table  : 
messageid
messagetitle 
.
.

#usersmessages table 
usersmessageid 
messageid
userid
.
.

现在我想从消息表中删除它没关系.但是当我删除messageid ='1'的消息时,例如它仍然存在于usersmessage上,我必须立即从这两个表中删除;

所以我使用以下查询:

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

然后我测试

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

但是这两个查询并没有说明这个任务.

1> Eric..：

---

难道你不能用分号分隔它们吗？

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'

---

不适用于SQL Server:','附近的语法不正确.

---

如果你在两个查询中执行此操作,那么你真的应该将它包装在一个事务中.至于在循环中运行删除查询,最好配制单个查询来执行所有删除操作.

2> angry kiwi..：

---

DELETE a.*, b.* 
FROM messages a 
LEFT JOIN usersmessages b 
ON b.messageid = a.messageid 
WHERE a.messageid = 1

翻译:从表消息中删除messageid = 1,如果表uersmessages有表消息的 messageid = messageid ,则删除该行的uersmessages表.

3> Quassnoi..：

---

您应该创建一个FOREIGN KEY具有ON DELETE CASCADE:

ALTER TABLE usersmessages
ADD CONSTRAINT fk_usermessages_messageid
FOREIGN KEY (messageid)
REFERENCES messages (messageid)
ON DELETE CASCADE

,或者在事务中使用两个查询来执行此操作:

START TRANSACTION;;

DELETE
FROM    usermessages
WHERE   messageid = 1

DELETE
FROM    messages
WHERE   messageid = 1;

COMMIT;

但是,事务只影响InnoDB表.

---

您*可以*在一个查询中从多个表中删除!http://dev.mysql.com/doc/refman/5.0/en/delete.html

4> Mike Trpcic..：

---

您有两种选择:

首先,在事务中执行两个语句:

BEGIN;
  DELETE FROM messages WHERE messageid = 1;
  DELETE FROM usermessages WHERE messageid = 1;
COMMIT;

或者,您可以使用外键设置ON DELETE CASCADE.这是更好的方法.

CREATE TABLE parent (
  id INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE child (
  id INT, parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);

您可以在此处阅读有关ON DELETE CASCADE的更多信息.

5> 小智..：

---

DELETE message.*, usersmessage.* from users, usersmessage WHERE message.messageid=usersmessage.messageid AND message.messageid='1'