SQL: Update all rows except one of each group

I have a table like this. Column “Mail sent” has either the values “0” or a date.

+-------------+-----------------+------------+
| Ordernumber |      Email      | Mail sent  |
+-------------+-----------------+------------+
|           1 | [e-mail] | 01.01.2018 |
|           2 | [e-mail] | 02.01.2018 |
|           3 | [e-mail] |          0 |
|           4 | [e-mail] | 04.01.2018 |
|           5 | [e-mail] | 05.01.2018 |
|           6 | [e-mail] | 06.01.2018 |
|           7 | [e-mail] | 07.01.2018 |
|           8 | [e-mail] |          0 |
|           9 | [e-mail] | 09.01.2018 |
|          10 | [e-mail]  |          0 |
|          11 | [e-mail]  |          0 |
+-------------+-----------------+------------+

I want to update the “Mail sent” column, so that for every Email there is at most only one date in “Mail sent”. Constraint: The lowest Ordernumber of every Email that has a date should keep the date, the rest should be set to “0”.

The result of the table should be:

+-------------+-----------------+------------+
| Ordernumber |      Email      | Mail sent  |
+-------------+-----------------+------------+
|           1 | [e-mail] | 01.01.2018 |
|           2 | [e-mail] |          0 |
|           3 | [e-mail] |          0 |
|           4 | [e-mail] | 04.01.2018 |
|           5 | [e-mail] |          0 |
|           6 | [e-mail] | 06.01.2018 |
|           7 | [e-mail] |          0 |
|           8 | [e-mail] |          0 |
|           9 | [e-mail] | 09.01.2018 |
|          10 | [e-mail]  |          0 |
|          11 | [e-mail]  |          0 |
+-------------+-----------------+------------+

Answer

You can use join:

update likethis lt join
       (select email, min(ordernumber) as minon
        from likethis
        where mailsent <> 0
        group by email
       ) lt2
       on lt.email = lt2.email
    set lt.mailsent = 0
    where lt.mailsent <> 0 and lt.ordernumber > lt2.minon;

Here is the db fiddle.