Skip to content
Advertisement

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 | john.d@mail.com | 01.01.2018 |
|           2 | john.d@mail.com | 02.01.2018 |
|           3 | anne.t@mail.com |          0 |
|           4 | anne.t@mail.com | 04.01.2018 |
|           5 | anne.t@mail.com | 05.01.2018 |
|           6 | jeff.k@mail.com | 06.01.2018 |
|           7 | jeff.k@mail.com | 07.01.2018 |
|           8 | jeff.k@mail.com |          0 |
|           9 | mary.k@mail.com | 09.01.2018 |
|          10 | sam.b@mail.com  |          0 |
|          11 | sam.b@mail.com  |          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 | john.d@mail.com | 01.01.2018 |
|           2 | john.d@mail.com |          0 |
|           3 | anne.t@mail.com |          0 |
|           4 | anne.t@mail.com | 04.01.2018 |
|           5 | anne.t@mail.com |          0 |
|           6 | jeff.k@mail.com | 06.01.2018 |
|           7 | jeff.k@mail.com |          0 |
|           8 | jeff.k@mail.com |          0 |
|           9 | mary.k@mail.com | 09.01.2018 |
|          10 | sam.b@mail.com  |          0 |
|          11 | sam.b@mail.com  |          0 |
+-------------+-----------------+------------+

Advertisement

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.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement