Inner join and Delete : Inner Join « Join « SQL / MySQL





Inner join and Delete

    
mysql>
mysql> CREATE TABLE Books
    -> (
    ->     BookID SMALLINT NOT NULL PRIMARY KEY,
    ->     BookTitle VARCHAR(60) NOT NULL,
    ->     Copyright YEAR NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO Books VALUES
    -> (12786, 'Notebook', 1934),
    -> (13331, 'C++', 1919),
    -> (14356, 'Opera', 1966),
    -> (15729, 'Sql Server', 1932),
    -> (16284, 'C', 1996),
    -> (17695, 'Pascal', 1980),
    -> (19264, 'Postcards', 1992),
    -> (19354, 'Oracle', 1993);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> CREATE TABLE Authors
    -> (
    ->     AuthID SMALLINT NOT NULL PRIMARY KEY,
    ->     AuthFN VARCHAR(20),
    ->     AuthMN VARCHAR(20),
    ->     AuthLN VARCHAR(20)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO Authors VALUES
    -> (1006, 'Hunter', 'S.', 'Thompson'),
    -> (1007, 'Joyce', 'Carol', 'Oates'),
    -> (1008, 'Black', NULL, 'Elk'),
    -> (1009, 'Rainer', 'Maria', 'Rilke'),
    -> (1010, 'John', 'Kennedy', 'Toole'),
    -> (1011, 'John', 'G.', 'Neihardt'),
    -> (1012, 'Annie', NULL, 'Proulx'),
    -> (1013, 'Alan', NULL, 'Watts'),
    -> (1014, 'Nelson', NULL, 'Algren');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> CREATE TABLE AuthorBook
    -> (
    ->     BookID SMALLINT NOT NULL,
    ->     AuthID SMALLINT NOT NULL,
    ->     PRIMARY KEY (AuthID, BookID)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO AuthorBook VALUES
    -> (1006, 14356),
    -> (1008, 15729),
    -> (1009, 12786),
    -> (1010, 17695),
    -> (1011, 15729),
    -> (1012, 19264),
    -> (1012, 19354),
    -> (1014, 16284);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> DELETE ab, b
    -> FROM Authors AS a INNER JOIN AuthorBook AS ab ON a.AuthID=ab.AuthID
    -> INNER JOIN Books AS b ON ab.BookID=b.BookID
    -> WHERE AuthLN='Watts';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql> drop table AuthorBook;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table Authors;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table Books;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
  










Related examples in the same category

1.INNER JOIN in MySQL
2.Creating Inner Joins and Cross Joins
3.To qualify the inner or cross join, you can use the ON or USING clause rather than the WHERE clause.
4.Qualify an inner or full join by specifying a condition in the WHERE clause
5.Inner Joins
6.Inner joins 2
7.Outer join then inner join
8.Inner join syntax
9.Query from a inner join query