sql多表关联删除 - update两个表




从一个查询中的两个表中删除 (6)

不需要JOINS:

DELETE m, um FROM messages m, usersmessages um

WHERE m.messageid = 1 

AND m.messageid = um.messageid 

我在mysql中有两个表

#messages table  : 
messageid
messagetitle 
.
.

#usersmessages table 
usersmessageid 
messageid
userid
.
.

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

所以我使用以下查询:

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

然后我测试

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

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


你不能用分号把它们分开吗?

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'

尝试这个..

DELETE a.*, b.*  
FROM table1 as a, table2 as b  
WHERE a.id=[Your value here] and b.id=[Your value here]

我让id作为样本列。

很高兴这有帮助。 :)


您应该使用ON DELETE CASCADE创建一个FOREIGN KEY

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表。


请试试这个

DELETE FROM messages,usersmessages

USING messages

INNER JOIN usermessages on (messages.messageid = usersmessages.messageid)

WHERE messages.messsageid='1'

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表。







mysql