1

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!

0
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.

-1
UPDATE mob,web
SET mob.date=web.date
WHERE mob.date IS NULL
      AND mob.id = web.id
  • 1
    Kindly write down some explanation about the syntax. For better understanding to the other professional. – Md Haidar Ali Khan Oct 19 '18 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.