Using DELETE LOW_PRIORITY command

    
/*

mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered         |
+---------+--------+----------+---------------------+
|    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 |
+---------+--------+----------+---------------------+
20 rows in set (0.01 sec)

mysql> DELETE LOW_PRIORITY FROM Orders
    -> WHERE BookID=103
    -> ORDER BY DateOrdered DESC
    -> LIMIT 1;
Query OK, 1 row affected (0.03 sec)

mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered         |
+---------+--------+----------+---------------------+
|    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 |
|    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 |
+---------+--------+----------+---------------------+
19 rows in set (0.00 sec)


*/

Drop table Orders;


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

select * from Orders;

DELETE LOW_PRIORITY FROM Orders
WHERE BookID=103
ORDER BY DateOrdered DESC
LIMIT 1;

select * from Orders;

Related examples in the same category

1.	Deleting Rows with DELETE
2.	Delete by JOIN
3.	Delete with JOIN 2
4.	Using the 'from join delete' Alternative to Delete Data
5.	Delete row with condition
6.	Delete statement with subquery (ERROR 1093 (HY000): You can't specify target table 'EmployeeS' for update in FROM clause)
7.	DELETE statement using the alternative.
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

Using DELETE LOW_PRIORITY command : Delete « Insert Delete Update « SQL / MySQL

Related examples in the same category