delete from two tables in one query

Question

hi , i have two tables in mysql

#messages table  : 
messageid
messagetitle 
.
.

#usersmessages table 
usersmessageid 
messageid
userid
.
.

now i want to delete from messages table it's ok . but when i delete message with messageid='1' for example it's still exists on usersmessage i have to delete from this two tables at once ;

so i use the following query :

DELTE 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 this two queries is not accoumplish this task .

Answer 1

Can't you just separate them by a semicolon?

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

OR

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

Answer 2

You should either create a FOREIGN KEY with 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 InnoDB tables, though.

Answer 3

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.

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.