title: Delete/Merge rows based on multiple columns
body: <p>I have the following table:</p>
  
  <pre><code>+------------+-----------+--------+----------+-------------+------------------+
  |    id      |  order_id | origin | type     | delivery_id | return_requested | 
  +------------+-----------+--------+----------+-------------+------------------+
  | 42         | 420       |  US    | DELIVERY | d-UUID      |        0         |
  +------------+-----------+--------+----------+-------------+------------------+
  | 43         | 420       |  US    | RETURN   | r-UUID      |        1         |
  +------------+-----------+--------+----------+-------------+------------------+
  | 48         | 520       |  US    | DELIVERY | d-UUID      |        0         |
  +------------+-----------+--------+----------+-------------+------------------+
  | 50         | 520       |  US    | RETURN   | r-UUID      |        1         |
  +------------+-----------+--------+----------+-------------+------------------+
  </code></pre>
  
  <p>Where <code>d-UUID</code> or <code>r-UUID</code> are unique ids with a prefix (<code>d</code> for delivery and <code>r</code> for return).</p>
  
  <p>Currently I changed the way I insert data in the database and instead of adding a new row every time, I am now finding the order_id and updating the existing row. </p>
  
  <p>For example if an order was returned, I will now find the one with id=42 and update it from type 'DELIVERY' to 'RETURN' and 'return_requested' from false to true while the <code>delivery_id</code> stays the same. 
  (the two columns type and return_requested are a bit redundant, but that's for another day)</p>
  
  <p>But I have a lot of old entries now with <code>delivery_id</code> beginning with <code>r-</code> which I want to delete/merge with the rows where the <code>order_id</code> is the same. </p>
  
  <p>I have the following SQL that finds these rows:</p>
  
  <pre><code>SELECT * FROM delivery
  WHERE order_id IN (
      SELECT order_id FROM delivery
      WHERE return_requested = 1
      AND delivery_id LIKE '%R-%'
  )
  </code></pre>
  
  <p>But I cannot figure out how to merge these rows. In the end I want to achieve this:</p>
  
  <pre><code>+------------+-----------+--------+----------+-------------+------------------+
  |    id      |  order_id | origin | type     | delivery_id | return_requested | 
  +------------+-----------+--------+----------+-------------+------------------+
  | 42         | 420       |  US    | RETURN   | d-UUID      |        1         |
  +------------+-----------+--------+----------+-------------+------------------+
  | 48         | 520       |  US    | RETURN   | d-UUID      |        1         |
  +------------+-----------+--------+----------+-------------+------------------+
  </code></pre>
  
  <p>EDIT: I am looing for an update/delete statement that mutates the data in the database, not a SELECT statement. </p>