Replace Null values with values from the same column by matching values in a different column

Question

I have a table with some null values in column "date":

platform   date         id
---------------------------
web        2018-10-10   1
mob                     1
mob                     1
web        2018-10-15   2
mob                     2
ntl        2018-10-09   3
web        2018-10-12   3
web        2018-10-11   4
mob                     3

I want to update null values in 'date' for 'mob' platform by matching the 'id' column from platform 'web'. The result should look like this:

platform   date         id
---------------------------
web        2018-10-10   1
mob        2018-10-10   1
mob        2018-10-10   1
web        2018-10-15   2
mob        2018-10-15   2
ntl        2018-10-09   3
web        2018-10-12   3
web        2018-10-11   4
mob        2018-10-12   3

Will really appreciate your help!

Answer 1

UPDATE `table` t1, `table` t2
SET t1.`date` = t2.`date`
WHERE t1.id = t2.id
  AND t1.platform = 'mob'
  AND t2.platform = 'web'
  AND t1.`date` IS NULL
  AND t2.`date` IS NOT NULL

PS. The solution assumes that in a subarray of the table which matched platform = 'web' AND t2.`date` IS NOT NULL condition the id value is unique. If not, a random value from possible values list will be used, and you must use a subquery instead of t2 table copy, which filters data by platform = 'web' condition, groups it by id and selects alone data value (maximal, for example) for each id value. If your MySQL version is 8+, you may do it in WITH clause.