DELETE statement using the alternative. : Delete « Insert Delete Update « SQL / MySQL






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

DELETE statement using the alternative.

       
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.01 sec)

mysql>
mysql>
mysql> INSERT INTO Books
    -> VALUES (101, 'C: Writing on Writing', 12),
    -> (102, 'Oracle', 17),
    -> (103, 'Opera', 23),
    -> (104, 'Notebook', 32),
    -> (105, 'Pascal', 6),
    -> (106, 'One Hundred Years of Solitude', 28);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

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.01 sec)

mysql>
mysql> INSERT INTO Orders VALUES (1001, 103, 1, '2004-10-12 12:30:00'),
    -> (1002, 101, 1, '2004-10-12 12:31:00'),
    -> (1003, 103, 2, '2004-10-12 12:34:00'),
    -> (1004, 104, 3, '2004-10-12 12:36:00'),
    -> (1005, 102, 1, '2004-10-12 12:41:00'),
    -> (1006, 103, 2, '2004-10-12 12:59:00'),
    -> (1007, 101, 1, '2004-10-12 13:01:00'),
    -> (1008, 103, 1, '2004-10-12 13:02:00'),
    -> (1009, 102, 4, '2004-10-12 13:22:00'),
    -> (1010, 101, 2, '2004-10-12 13:30:00'),
    -> (1011, 103, 1, '2004-10-12 13:32:00');
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> DELETE FROM Orders
    -> USING Books, Orders
    -> WHERE Books.BookID=Orders.BookID
    -> AND Books.BookName='Where I\'m Calling From';
Query OK, 0 rows affected (0.00 sec)

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

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








Related examples in the same category

1.Deleting Rows with DELETE
2.Using DELETE LOW_PRIORITY command
3.Delete by JOIN
4.Delete with JOIN 2
5.Using the 'from join delete' Alternative to Delete Data
6.Delete row with condition
7.Delete statement with subquery (ERROR 1093 (HY000): You can't specify target table 'EmployeeS' for update in FROM clause)
8.Joining Tables in a DELETE Statement
9.Delete with where clause
10.Delete ignore
11.Adding Subqueries to Your DELETE Statements
12.Delete All Threads Except the Last 500
13.Delete rows from multiple tables