I have two tables in MySQL
#messages table : messageidmessagetitle ..#usersmessages table usersmessageid messageiduserid..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 .
Best Answer
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 = 1translation: 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 usersmessagesADD CONSTRAINT fk_usermessages_messageidFOREIGN KEY (messageid)REFERENCES messages (messageid)ON DELETE CASCADE, or do it using two queries in a transaction:
START TRANSACTION;;DELETEFROM usermessagesWHERE messageid = 1DELETEFROM messagesWHERE messageid = 1;COMMIT;Transaction affects only InnoDB tables, though.
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_idYou 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 umWHERE m.messageid = 1 AND m.messageid = um.messageid DELETE message.*, usersmessage.* from users, usersmessage WHERE message.messageid=usersmessage.messageid AND message.messageid='1'Try this please
DELETE FROM messages,usersmessagesUSING messagesINNER 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_testWHERE 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 tablefor($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