I have a table like this. Column “Mail sent” has either the values “0” or a date.
x
+-------------+-----------------+------------+
| 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.