Deleting a row in MySQL

Question

I have a table:

NACHRICHT_ID | VERFASSER_USERNAME | BETREFF | TEXT |  DATUM    | EMPAENGER_ID
------------------------------------------------------------------------------
             |                    |         |      | 2009-07-01|  
     1       |          h         | hfgh    | hfgh |  23:15:10 |    31
------------------------------------------------------------------------------
             |                    |         |      | 2009-07-02|  
     3       |          h         | hfgh    | hfgh |  00:30:10 |    31
------------------------------------------------------------------------------
             |                    |         |      | 2009-07-02|  
     4       |          q         | hfgh    | hi   |  11:42:06 |    31
------------------------------------------------------------------------------
             |                    |         |      | 2009-07-02|  
     5       |          h         | hfgh    | hfgh |  12:53:56 |    31
------------------------------------------------------------------------------
             |                    |         |      | 2009-07-02|  
     6       |          h         | hfgh    | hfgh |  13:19:53 |    31
------------------------------------------------------------------------------
             |                    |         |      | 2009-07-02|  
     7       |          h         | hfgh    | hfgh |  13:20:18 |    31
------------------------------------------------------------------------------
             |                    |         |      | 2009-07-02|  
     8       |         mkd        | hfgh    | hfgh |  13:47:32 |    31
------------------------------------------------------------------------------
             |                    |         |      | 2009-07-02|  
     9       |          h         | hfgh    | hfgh |  16:42:10 |    31
------------------------------------------------------------------------------

I'm trying to delete the Row where NACHRICHT_ID = 3:

DELETE FROM NACHRICHTEN JOIN 
                    NACHRICHTEN_EMPFAENGER_MAP USING (NACHRICHT_ID)
                    WHERE EMPFAENGER_ID = 31 && NACHRICHT_ID = 3 ;

But it isn't working. What am I doing wrong?

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/delete.html — answer is there

Answer 2

DELETE FROM 'table' WHERE NACHRICHT_ID = 3

Answer 3

DELETE FROM NACHRICHTEN 
WHERE EMPFAENGER_ID = 31 && NACHRICHT_ID = 3;

Answer 4

Maybe you all got me wrong .. in this case : the delete WILL HAPPEN .. AFTER A JOIN !!! (NACHRICHTEN <--> NACHRICHTEN_EMPFAENGER_MAP ) and the EMPFAENGER_ID is the NACHRICHTEN_EMPFAENGER_MAP .. so all answers are wrong

Answer 5

A join is an operation that creates a temporary table based on data from several other tables and retrieves values from this temporary table, then discards it (this is the concept, not the actual implementation).

So deleting "from" or "after a join" is an idea that does not make much sense. But if I unterstand correctly, you just want to delete the message (Nachricht). You should just delete this message from the message table, as was shown in the other answers.

After that, if you do your SELECT * FROM ... JOIN ... , you'll see that the message is not there in the join result either.

I hope that helps.

PS: There's no need to yell at the people who are trying to help you.

Answer 6

I guess you are using MyISAM as your storage engine, which unfortunately doesn't support foreign keys.

So, given the following tables:

A
---
A_ID (PK), A_SOMETHING

B
---
B_ID (PK), B_SOMETHING

C
---
A_ID (PK, FK), B_ID (PK, FK)

If you want delete all As which are linked to B_ID = 13, you can do the following.

DELETE FORM A WHERE A_ID IN (SELECT A_ID FROM C WHERE B_ID = 13);

Or, if you want to delete all As where the linked B's B_SOMETHING value is 'Foo':

DELETE FORM A WHERE A_ID IN
    (SELECT C.A_ID FROM B,C
         WHERE B.B_ID = C.B_ID
           AND B.B_SOMETHING = 'Foo'
    );

After, you have some cleanup to do:

DELETE FROM C WHERE A_ID NOT IN (SELECT A_ID FROM A);

Now, doing this all manually is a pain and you are better off using a storage engine with support for Foreign Keys (InnoDB).

You should of provided a screen shot of all the relevant tables, not just the joined result. It would of made your question that much clearer.

Answer 7

Your question is unclear.

You have 3 tables:

• NACHRICHTEN = News Items
• NACHRICHTEN_EMPFAENGER_MAP = News Items to Recipients Map
• EMPFAENGER (I am guessing the name) = Recipients

Do you want to delete the news item? Then do this:

delete from NACHRICHTEN where NACHRICHT_ID = 3;

If you want to delete the mapping between the news item with id 3 and the recipient with id 31, then do this:

delete from NACHRICHTEN_EMPFAENGER_MAP where NACHRICHT_ID=3 and EMPFAENGER_ID=31;

If you want to delete the actual recipient with id 31 then do this (assuming the table is called EMPFAENGER)

delete from EMPFAENGER where EMPFAENGER_ID = 31;

If you want to do something else, ask the question with more details please.