Delete statement based on table joins : Delete « Insert Update Delete « MySQL Tutorial






  1. MySQL Tutorial
  2. Insert Update Delete
  3. Delete
mysql>
mysql>
mysql> CREATE TABLE Books(
    ->    BookID SMALLINT NOT NULL PRIMARY KEY,
    ->    BookName VARCHAR(40) NOT NULL,
    ->    InStock SMALLINT NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE TABLE Orders(
    ->    OrderID SMALLINT NOT NULL PRIMARY KEY,
    ->    BookID SMALLINT NOT NULL,
    ->    Quantity TINYINT (40) NOT NULL DEFAULT 1,
    ->    DateOrdered TIMESTAMP,
    ->    FOREIGN KEY (BookID) REFERENCES Books (BookID)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql>
mysql> INSERT INTO Books VALUES (101, 'Java', 12),
    ->                          (102, 'PHP', 17),
    ->                          (103, 'MySQL', 23),
    ->                          (104, 'Perl', 32),
    ->                          (105, 'Pyton', 6),
    ->                          (106, 'www.java2s.com', 28);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO Orders VALUES (1001, 103, 1, '2001-11-12 12:30:00'),
    ->                           (1002, 101, 1, '2002-10-16 12:31:00'),
    ->                           (1003, 103, 2, '2003-02-11 12:34:00'),
    ->                           (1004, 104, 3, '2004-01-19 12:36:00'),
    ->                           (1005, 102, 1, '2005-12-17 12:41:00'),
    ->                           (1006, 103, 2, '2006-10-18 12:59:00'),
    ->                           (1007, 101, 1, '2004-11-21 13:01:00'),
    ->                           (1008, 103, 1, '2014-10-16 13:02:00'),
    ->                           (1009, 102, 4, '1994-09-02 13:22:00'),
    ->                           (1010, 101, 2, '1995-10-04 13:30:00'),
    ->                           (1011, 103, 1, '1996-08-12 13:32:00'),
    ->                           (1012, 105, 1, '2004-10-03 13:40:00'),
    ->                           (1013, 106, 2, '2002-05-12 13:44:00'),
    ->                           (1014, 103, 1, '2001-10-01 14:01:00'),
    ->                           (1015, 106, 1, '1997-05-05 14:05:00'),
    ->                           (1016, 104, 2, '1998-10-07 14:28:00'),
    ->                           (1017, 105, 1, '2004-03-12 14:31:00'),
    ->                           (1018, 102, 1, '2004-10-21 14:32:00'),
    ->                           (1019, 106, 3, '1991-01-30 14:49:00'),
    ->                           (1020, 103, 1, '1990-10-12 14:51:00');
Query OK, 20 rows affected (0.05 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName       | InStock |
+--------+----------------+---------+
|    101 | Java           |      12 |
|    102 | PHP            |      17 |
|    103 | MySQL          |      23 |
|    104 | Perl           |      32 |
|    105 | Pyton          |       6 |
|    106 | www.java2s.com |      28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)

mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered         |
+---------+--------+----------+---------------------+
|    1001 |    103 |        1 | 2001-11-12 12:30:00 |
|    1002 |    101 |        1 | 2002-10-16 12:31:00 |
|    1003 |    103 |        2 | 2003-02-11 12:34:00 |
|    1004 |    104 |        3 | 2004-01-19 12:36:00 |
|    1005 |    102 |        1 | 2005-12-17 12:41:00 |
|    1006 |    103 |        2 | 2006-10-18 12:59:00 |
|    1007 |    101 |        1 | 2004-11-21 13:01:00 |
|    1008 |    103 |        1 | 2014-10-16 13:02:00 |
|    1009 |    102 |        4 | 1994-09-02 13:22:00 |
|    1010 |    101 |        2 | 1995-10-04 13:30:00 |
|    1011 |    103 |        1 | 1996-08-12 13:32:00 |
|    1012 |    105 |        1 | 2004-10-03 13:40:00 |
|    1013 |    106 |        2 | 2002-05-12 13:44:00 |
|    1014 |    103 |        1 | 2001-10-01 14:01:00 |
|    1015 |    106 |        1 | 1997-05-05 14:05:00 |
|    1016 |    104 |        2 | 1998-10-07 14:28:00 |
|    1017 |    105 |        1 | 2004-03-12 14:31:00 |
|    1018 |    102 |        1 | 2004-10-21 14:32:00 |
|    1019 |    106 |        3 | 1991-01-30 14:49:00 |
|    1020 |    103 |        1 | 1990-10-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)

mysql>
mysql>
mysql> DELETE FROM Orders
    -> USING Books, Orders
    -> WHERE Books.BookID=Orders.BookID
    ->    AND Books.BookName='MySQL';
Query OK, 7 rows affected (0.01 sec)

mysql>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName       | InStock |
+--------+----------------+---------+
|    101 | Java           |      12 |
|    102 | PHP            |      17 |
|    103 | MySQL          |      23 |
|    104 | Perl           |      32 |
|    105 | Pyton          |       6 |
|    106 | www.java2s.com |      28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)

mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered         |
+---------+--------+----------+---------------------+
|    1002 |    101 |        1 | 2002-10-16 12:31:00 |
|    1004 |    104 |        3 | 2004-01-19 12:36:00 |
|    1005 |    102 |        1 | 2005-12-17 12:41:00 |
|    1007 |    101 |        1 | 2004-11-21 13:01:00 |
|    1009 |    102 |        4 | 1994-09-02 13:22:00 |
|    1010 |    101 |        2 | 1995-10-04 13:30:00 |
|    1012 |    105 |        1 | 2004-10-03 13:40:00 |
|    1013 |    106 |        2 | 2002-05-12 13:44:00 |
|    1015 |    106 |        1 | 1997-05-05 14:05:00 |
|    1016 |    104 |        2 | 1998-10-07 14:28:00 |
|    1017 |    105 |        1 | 2004-03-12 14:31:00 |
|    1018 |    102 |        1 | 2004-10-21 14:32:00 |
|    1019 |    106 |        3 | 1991-01-30 14:49:00 |
+---------+--------+----------+---------------------+
13 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> drop table Orders;
Query OK, 0 rows affected (0.02 sec)

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

mysql>
mysql>








7.3.Delete
7.3.1.The DELETE statement
7.3.2.Using the LIMIT keyword to ensure only the number of rows you specify are deleted
7.3.3.Without the WHERE clause, all records from the database are deleted by default
7.3.4.Delete statement based on table joins
7.3.5.Delete statement based on table joins (tableName.*)
7.3.6.Delete on row with LOW_PRIORITY