Delete with INNER JOIN : Join Delete : Join SQL / MySQL


SQL / MySQL » Join » Join Delete »

 

Delete with INNER JOIN



/*

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

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)

*/       
       
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;



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


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)
)
ENGINE=INNODB;


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,
   PRIMARY KEY (AuthID, ArticleID),
   FOREIGN KEY (AuthIDREFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleIDREFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO AuthorArticle VALUES (100614356)
                              (100815729)
                              (100912786)
                              (101017695),
                              (101115729)
                              (101219264)
                              (101219354)
                              (101416284);
  
Select * from Authors;
  
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';

Select * from Authors;

           
       



Leave a Comment / Note


 
Verification is used to prevent unwanted posts (spam). .

Follow Navioo On Twitter

SQL / MySQL

 Navioo Join
» Join Delete