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

Delete from two tables in one query

提问by mehdi

I have two tables in MySQL

我在 MySQL 中有两个表

#messages table  : 
messageid
messagetitle 
.
.

#usersmessages table 
usersmessageid 
messageid
userid
.
.

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.

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

I used the following query :

我使用了以下查询：

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

Then I test

然后我测试

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

But these two queries are not accomplishing this task .

但是这两个查询并没有完成这个任务。

回答by Eric

Can't you just separate them by a semicolon?

不能用分号隔开吗？

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

OR

或者

Just use INNER JOINas below

只需INNER JOIN如下使用

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

回答by angry kiwi

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

translation: delete from table messageswhere messageid =1, if table uersmessageshas messageid = messageid of table messages, delete that row of uersmessagestable.

翻译：从messageid =1 的表消息中删除，如果表uersmessages有 messageid = messageid 表消息，则删除uersmessages表的那一行。

回答by Quassnoi

You should either create a FOREIGN KEYwith ON DELETE CASCADE:

您应该创建一个FOREIGN KEYwith ON DELETE CASCADE：

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

, or do it using two queries in a transaction:

，或者在一个事务中使用两个查询：

START TRANSACTION;;

DELETE
FROM    usermessages
WHERE   messageid = 1

DELETE
FROM    messages
WHERE   messageid = 1;

COMMIT;

Transaction affects only InnoDBtables, though.

但是，事务仅影响InnoDB表。

回答by Mike Trpcic

You have two options:

您有两个选择：

First, do two statements inside a transaction:

首先，在一个事务中做两条语句：

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

Or, you could have ON DELETE CASCADE set up with a foreign key. This is the better approach.

或者，您可以使用外键设置 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
);

You can read more about ON DELETE CASCADE here.

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

回答by johan

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

回答by Fred Yates

no need for JOINS:

不需要连接：

DELETE m, um FROM messages m, usersmessages um

WHERE m.messageid = 1 

AND m.messageid = um.messageid 

回答by Mark Angelo Pascual

The OP is just missing the table aliases after the delete

删除后 OP 只是缺少表别名

DELETE t1, t2 
FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id 
WHERE t1.id = some_id

回答by Amit

Try this please

请试试这个

DELETE FROM messages,usersmessages

USING messages

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

WHERE messages.messsageid='1'

回答by John Oliver Amurao

Try this..

尝试这个..

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

I let idas a sample column.

我让id作为示例列。

Glad this helps. :)

很高兴这有帮助。:)

回答by Vidya

You can also use like this, to delete particular value when both the columns having 2 or many of same column name.

您也可以这样使用，当两列具有 2 个或多个相同的列名时删除特定值。

DELETE project , create_test  FROM project INNER JOIN create_test
WHERE project.project_name='Trail' and  create_test.project_name ='Trail' and project.uid= create_test.uid = '1';