Delete from two tables in one query

Solution 1:

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 JOIN as below

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

Solution 2:

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

translation: delete from table messages where messageid =1, if table uersmessages has messageid = messageid of table messages, delete that row of uersmessages table.

Solution 3:

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.