comment on

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]

In reply to MySQL DELETE with INNER JOIN by lancer

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.