Delete from two tables in one query

I have two tables in 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.

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 .

11 Answers

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' 

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.

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.

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.

no need for JOINS:

DELETE m, um FROM messages m, usersmessages um WHERE m.messageid = 1 AND m.messageid = um.messageid 

The OP is just missing the table aliases after the delete

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

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

Try this please

DELETE FROM messages,usersmessages USING messages INNER JOIN usermessages on (messages.messageid = usersmessages.messageid) WHERE messages.messsageid='1' 

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 id as a sample column.

Glad this helps. :)

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

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'; 

there's another way which is not mentioned here (I didn't fully test it's performance yet), you could set array for all tables -> rows you want to delete as below

// set your tables array $array = ['table1', 'table2', 'table3']; // loop through each table for($i = 0; $i < count($array); $i++){ // get each single array $single_array = $array[$i]; // build your query $query = "DELETE FROM $single_array WHERE id = 'id'"; // prepare the query and get the connection $data = con::GetCon()->prepare($query); // execute the action $data->execute(); } 

then you could redirect the user to the home page.

header('LOCATION:' . $home_page); 

hope this will help someone :)

Thanks