2

Given the following table data:

+------+---------+----------+----------+-------------+------------+-----------------+---------------------+
| 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 |
+------+---------+----------+----------+-------------+------------+-----------------+---------------------+

Using MySQL - how can I delete all rows containing the state 'created' only when the table also contains a row with the state 'unpacked' given the order line id?

crmepham
  • 4,676
  • 19
  • 80
  • 155

1 Answers1

7

You would use delete . . . with join:

delete todelete
    from t todelete join
         t
         on t.orderLineId = todelete.orderLineId
    where todelete.state = 'created' and t.state = 'unpacked';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @crm MySQL often has performance problems with `WHERE ... IN (SELECT ...)` so I always prefer joins. – Barmar Sep 08 '17 at 19:53
  • I would agree that this version is the better option here. Simple is beautiful ... and performant! – Ted at ORCL.Pro Sep 08 '17 at 20:06
  • 1
    @Barmar . . . More importantly, that construct will not work for `delete`, because the table being modified is in the subquery. By definition, this is more performant, because this will work. – Gordon Linoff Sep 08 '17 at 21:16
  • True. The performance issue comes up analogous `SELECT` statements. – Barmar Sep 08 '17 at 21:18