tags:

views:

832

answers:

3

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 .

+3  A: 

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'

Eric
what happen if i want to put this code in a loop ? i know i can put the semicolon absolutely .
mehdi
It can be put in a loop. You can do it programmtically and send the message id as a parameter.
Eric
@mehdi: IT would perform the delete over and over again in a loop...
Eric
It can be put in a loop. You can do it programmtically and send the message id as a parameter. As long as you don't keep on sending it the same id it won't do the delete over and over.
Eric
@Eric Yeah , i Test This Query it working perfectly ;) tanks . but i do the same query before i post but it's dosen't work because i don't say messages.messageid='1' i just say messageid='1' ; tanks any way .
mehdi
No problem! Glad i could help.
Eric
@Eric i don't think it's good idea to create too queries to do simple task like this as long as i can do that in just one query .
mehdi
@Mehdi I hear you. But is the second query I gave you the one that worked?
Eric
@Eric Yes . this query is what i need . the second query is working for me .
mehdi
If you do this in two queries, you really should wrap this in a transaction. As for running delete queries in a loop, you are better off formulating a single query to do all the deletes.
JohnFx
Checkout another example of delete query with joins http://www.bennadel.com/blog/939-Using-A-SQL-JOIN-In-A-SQL-DELETE-Statement-Thanks-Pinal-Dave-.htm
Babar
+3  A: 

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.

Quassnoi
You *can* delete from multiple tables in one query! http://dev.mysql.com/doc/refman/5.0/en/delete.html
txwikinger
yes you can! But to me...it's easier to do what my answer said.
Eric
Ummm... right, you can :)
Quassnoi
I added the cascade on a similar table of mine. When I attempted to delete from 1 table only, it deleted the record and left the related one orphaned. What good is the constraint?
barfoon
@barfoon: is your table `InnoDB`?
Quassnoi
@Quassnoi Oops! I definitely thought it was, what a goof. All tables are MyISAM. Can I change all of them on the fly with data in them? Or is there issues with that?
barfoon
@barfoon: yes you can but be aware that the `FULLTEXT` and `SPATIAL` indexes will not work in `InnoDB`.
Quassnoi
+2  A: 

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.

Mike Trpcic