CODEWITHSUNDEEP
sqlmysql
mehdi
mehdi

Reputation: 9502

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 .

Upvotes: 114

Views: 576765

Answers (11)

Mark Angelo Pascual
Mark Angelo Pascual

Reputation: 91

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

Upvotes: 9

Midz Elwekil
Midz Elwekil

Reputation: 441

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

Upvotes: 0

Vidya
Vidya

Reputation: 73

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

Upvotes: 0

John Oliver Amurao
John Oliver Amurao

Reputation: 11

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. :)

Upvotes: 1

Fred Yates
Fred Yates

Reputation: 91

no need for JOINS: 

DELETE m, um FROM messages m, usersmessages um

WHERE m.messageid = 1 

AND m.messageid = um.messageid

Upvotes: 9

angry kiwi
angry kiwi

Reputation: 11445

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.

Upvotes: 56

johan
johan

Reputation: 61

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

Upvotes: 6

Amit
Amit

Reputation: 21

Try this please 

DELETE FROM messages,usersmessages

USING messages

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

WHERE messages.messsageid='1'

Upvotes: 2

Eric
Eric

Reputation: 8078

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'

Upvotes: 167

Quassnoi
Quassnoi

Reputation: 425331

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.

Upvotes: 19

Mike Trpcic
Mike Trpcic

Reputation: 25649

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.

Upvotes: 7

Related Questions