mysql - 如何从具有一个值的表中删除一行，其中表包含具有不同值的第二行？

给出下表数据：

+------+---------+----------+----------+-------------+------------+-----------------+---------------------+
| id   | version | quantity | state    | orderLineId | locationId | stockMoveTaskId | created             |
+------+---------+----------+----------+-------------+------------+-----------------+---------------------+
| 3277 |       0 |        2 | created  |      169108 |       5692 |            NULL | 2017-09-07 14:55:41 |
| 3073 |       0 |        2 | unpacked |      169108 |       5692 |            NULL | 2017-09-07 09:40:35 |
+------+---------+----------+----------+-------------+------------+-----------------+---------------------+

使用mysql-只有当表还包含一个状态为“unpacted”的行（给定订单行id）时，我如何删除所有包含状态“created”的行？

最佳答案

您可以使用delete。……使用join：

delete todelete
    from t todelete join
         t
         on t.orderLineId = todelete.orderLineId
    where todelete.state = 'created' and t.state = 'unpacked';