Delete with JOIN 2 : Delete « Insert Delete Update « SQL / MySQL

1. SQL / MySQL
2. Insert Delete Update
3. Delete

Delete with JOIN 2

    
Drop table Books;
Drop table Orders;       
       
CREATE TABLE Books
(
   BookID SMALLINT NOT NULL PRIMARY KEY,
   BookName VARCHAR(40) NOT NULL,
   InStock SMALLINT NOT NULL
)
ENGINE=INNODB;


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;


INSERT INTO Orders VALUES (1001, 103, 1, '2004-01-12 12:30:00'),
                          (1002, 101, 1, '2001-02-12 12:31:00'),
                          (1003, 103, 2, '2002-03-12 12:34:00'),
                          (1004, 104, 3, '2003-04-12 12:36:00'),
                          (1005, 102, 1, '2004-05-12 12:41:00'),
                          (1006, 103, 2, '2001-06-12 12:59:00'),
                          (1007, 101, 1, '2002-07-12 13:01:00'),
                          (1008, 103, 1, '2003-08-12 13:02:00'),
                          (1009, 102, 4, '2004-09-12 13:22:00'),
                          (1010, 101, 2, '2005-11-12 13:30:00'),
                          (1011, 103, 1, '2006-12-12 13:32:00'),
                          (1012, 105, 1, '2001-02-12 13:40:00'),
                          (1013, 106, 2, '2002-04-12 13:44:00'),
                          (1014, 103, 1, '2003-06-12 14:01:00'),
                          (1015, 106, 1, '2005-01-12 14:05:00'),
                          (1016, 104, 2, '2003-11-12 14:28:00'),
                          (1017, 105, 1, '2002-03-12 14:31:00'),
                          (1018, 102, 1, '2001-05-12 14:32:00'),
                          (1019, 106, 3, '2003-07-12 14:49:00'),
                          (1020, 103, 1, '2004-01-12 14:51:00');


INSERT INTO Books VALUES (101, 'Writing', 12),
                         (102, 'News', 17),
                         (103, 'Angels', 23),
                         (104, 'Poet', 32),
                         (105, 'Dunces', 6),
                         (106, 'Solitude', 28);

select * from Orders;

DELETE FROM Orders
USING Books, Orders
WHERE Books.BookID=Orders.BookID
   AND Books.BookName='Poet';

select * from Orders;

           
         
    
    
    
  

Related examples in the same category