This is not strictly related to Perl, but it comes up often enough that I think it would be useful to have a copy of it here.

I've copied it from elsewhere on the net.

UPDATE: I have cleaned up and fixed the code and now the example is functional. I have also updated the comments.

This example demonstrates the use of INNER JOIN-s in MySQL DELETE statements.

It's possible to join multiple tables in a delete statement, all of which are used to determine which rows to delete. But the actual deletion is only performed on those tables that are explicitly listed for this, the other tables are not touched. In this example, only rows from tables 'ab' and 'b' ('AuthorArticle' and 'Articles') are deleted. The table 'a' ('Authors') is left intact. 


/********************************************************************

mysql> Select * from Authors;
+--------+-----------------+------------------+----------------+
| AuthID | AuthorFirstName | AuthorMiddleName | AuthorLastName |
+--------+-----------------+------------------+----------------+
|   1006 | Henry           | S.               | Thompson       |
|   1007 | Jason           | Carol            | Oak            |
|   1008 | James           | NULL             | Elk            |
|   1009 | Tom             | M                | Ride           |
|   1010 | Jack            | K                | Ken            |
|   1011 | Mary            | G.               | Lee            |
|   1012 | Annie           | NULL             | Watts          |
|   1013 | Alan            | NULL             | Wang           |
|   1014 | Nelson          | NULL             | Yin            |
+--------+-----------------+------------------+----------------+
9 rows in set (0.01 sec)


mysql> Select * from AuthorArticle;
+--------+-----------+
| AuthID | ArticleID |
+--------+-----------+
|   1006 |     14356 |
|   1008 |     15729 |
|   1009 |     12786 |
|   1010 |     17695 |
|   1011 |     15729 |
|   1012 |     19264 |
|   1012 |     19354 |
|   1014 |     16284 |
+--------+-----------+
8 rows in set (0.00 sec)


mysql> Select * from Articles;    
+-----------+-------------------+-----------+
| ArticleID | ArticleTitle      | Copyright |
+-----------+-------------------+-----------+
|     12786 | How write a paper |      1934 |
|     13331 | Publish a paper   |      1919 |
|     14356 | Sell a paper      |      1966 |
|     15729 | Buy a paper       |      1932 |
|     16284 | Conferences       |      1996 |
|     17695 | Journal           |      1980 |
|     19264 | Information       |      1992 |
|     19354 | AI                |      1993 |
+-----------+-------------------+-----------+
8 rows in set (0.00 sec)



mysql> DELETE ab, b
    -> FROM Authors AS a INNER JOIN AuthorArticle AS ab ON a.AuthID=ab
+.AuthID 
    ->    INNER JOIN Articles AS b ON ab.ArticleID=b.ArticleID
    -> WHERE AuthorFirstName='Tom';
Query OK, 2 rows affected (0.00 sec)



mysql> Select * from Authors;
+--------+-----------------+------------------+----------------+
| AuthID | AuthorFirstName | AuthorMiddleName | AuthorLastName |
+--------+-----------------+------------------+----------------+
|   1006 | Henry           | S.               | Thompson       |
|   1007 | Jason           | Carol            | Oak            |
|   1008 | James           | NULL             | Elk            |
|   1009 | Tom             | M                | Ride           |
|   1010 | Jack            | K                | Ken            |
|   1011 | Mary            | G.               | Lee            |
|   1012 | Annie           | NULL             | Watts          |
|   1013 | Alan            | NULL             | Wang           |
|   1014 | Nelson          | NULL             | Yin            |
+--------+-----------------+------------------+----------------+
9 rows in set (0.00 sec)


mysql> Select * from AuthorArticle;
+--------+-----------+
| AuthID | ArticleID |
+--------+-----------+
|   1006 |     14356 |
|   1008 |     15729 |
|   1010 |     17695 |
|   1011 |     15729 |
|   1012 |     19264 |
|   1012 |     19354 |
|   1014 |     16284 |
+--------+-----------+
7 rows in set (0.00 sec)


mysql> Select * from Articles;
+-----------+-----------------+-----------+
| ArticleID | ArticleTitle    | Copyright |
+-----------+-----------------+-----------+
|     13331 | Publish a paper |      1919 |
|     14356 | Sell a paper    |      1966 |
|     15729 | Buy a paper     |      1932 |
|     16284 | Conferences     |      1996 |
|     17695 | Journal         |      1980 |
|     19264 | Information     |      1992 |
|     19354 | AI              |      1993 |
+-----------+-----------------+-----------+
7 rows in set (0.00 sec)

********************************************************************/

       


Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;



CREATE TABLE Articles (
   ArticleID SMALLINT NOT NULL PRIMARY KEY,
   ArticleTitle VARCHAR(60) NOT NULL,
   Copyright YEAR NOT NULL
);


INSERT INTO Articles VALUES (12786, 'How write a paper', 1934),
                            (13331, 'Publish a paper', 1919),
                            (14356, 'Sell a paper', 1966),
                            (15729, 'Buy a paper', 1932),
                            (16284, 'Conferences', 1996),
                            (17695, 'Journal', 1980),
                            (19264, 'Information', 1992),
                            (19354, 'AI', 1993);


CREATE TABLE Authors (
   AuthID SMALLINT NOT NULL PRIMARY KEY,
   AuthorFirstName VARCHAR(20),
   AuthorMiddleName VARCHAR(20),
   AuthorLastName VARCHAR(20)
);


INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson'),
                           (1007, 'Jason', 'Carol', 'Oak'),
                           (1008, 'James', NULL, 'Elk'),
                           (1009, 'Tom', 'M', 'Ride'),
                           (1010, 'Jack', 'K', 'Ken'),
                           (1011, 'Mary', 'G.', 'Lee'),
                           (1012, 'Annie', NULL, 'Peng'),
                           (1013, 'Alan', NULL, 'Wang'),
                           (1014, 'Nelson', NULL, 'Yin');


CREATE TABLE AuthorArticle (
   AuthID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL
);


INSERT INTO AuthorArticle VALUES (1006, 14356), 
                              (1008, 15729), 
                              (1009, 12786), 
                              (1010, 17695),
                              (1011, 15729), 
                              (1012, 19264), 
                              (1012, 19354), 
                              (1014, 16284);
  
Select * from Authors;
Select * from AuthorArticle;
Select * from Articles;

  
DELETE ab, b
FROM Authors AS a INNER JOIN AuthorArticle AS ab ON a.AuthID=ab.AuthID
   INNER JOIN Articles AS b ON ab.ArticleID=b.ArticleID
WHERE AuthorFirstName='Tom';


Select * from Authors;
Select * from AuthorArticle;
Select * from Articles;

[download]

Replies are listed 'Best First'.
Re: MySQL DELETE with INNER JOIN
by JavaFan (Canon) on Mar 03, 2011 at 15:57 UTC 
    mysql> DELETE ab, b
    -> FROM Authors AS a INNER JOIN AuthorArticle AS ab ON a.AuthID=ab
+.AuthID
    ->    INNER JOIN Articles AS b ON ab.ArticleID=b.ArticleID
    -> WHERE AuthorLastName='Tom';
Query OK, 0 rows affected (0.01 sec)

    [download]
    You may want to consider an example that actually deletes something.

    But don't post it here. This site is about Perl, not MySQL. The MySQL documentation explains it in detail, giving alternative syntaxes as well.

[reply]
[d/l]

      I know it doesn't work.

      I just think it is a good example, if the code can be fixed. I have done this once already, when I first saw this example on the net. Then it proved to be a very valuable learning resource for me.

      I needed this knowledge again today, so I've google'd it up.

      I decided to post it here so that others can learn from it. I know it's not Perl, but I remember reading here somewhere that about 5% of the posts can be off-topic, and, it's possible to use MySQL from Perl, so I believe it can be useful to at least a few people on this site.

[reply]
Re: MySQL DELETE with INNER JOIN
by ww (Archbishop) on Mar 03, 2011 at 20:33 UTC
    Gee, I must have missed the announcement.

    When was it that the Monastery began offering free, off-site archiving for non-Perl projects?

[reply]
Re: MySQL DELETE with INNER JOIN
by locked_user sundialsvc4 (Abbot) on Mar 23, 2011 at 19:24 UTC

    To my way of thinking, especially with regard to DELETE, this is an illustration of why “obvious simplicity always wins.”   I do not want a “clever” way to delete 10,000 somethings.   Instead, I want to write logic that, say, queries for the keys to be deleted, and then, in a transaction, deletes those keys.   (Possibly a sensibly-sized group at a time, blah, blah, blah...)   The resulting code may or may not be “efficient,” in some people’s minds, but (a) it is certainly efficient enough, and (b) it makes it easy to dump out a list of the keys that you think you’re going to delete while you are debugging the thing, without actually doing it (yet).   My main objective in writing code like this is to make damm sure that I am not about to shoot off my own foot; not to shoot it off a little bit faster.

    As far as the “appropriateness” of this post goes, well, yes, it is not quite “on-topic,” but then again, most of us do use MySQL quite-a-bit in connection with Perl, so IMHO it is “tangentally related” enough not to warrant “consideration” by the mods.

Back to Meditations