MySQL Data Cleanup

Question

I'm trying to clear some data in MySQL, and I'm not sure how the most efficient way to fix the following. I have three columns A, B and C. They often take the same value. If A and B are the same or C matches, then I want, if possible, to populate the other option with NULL. B and C do not affect the value for A. For example:

-------------------
|A    |B    |C    |
-------------------
|1    |2    |3    |
|1    |2    |NULL |
|2    |5    |8    |
|2    |NULL |8    |
|3    |NULL |9    |
|3    |NULL |NULL |
-------------------

In the line of example 2 above, column C should be filled with 3 and line 4, column B should be 5. When I have only two options, we then fill in accordingly. Therefore row 6, column C must be 9, and row 5 of column B and column 6 of row 6 remain NULL. How can I write a script to solve this problem, so that if B or C is not NULL, we fill it with the other values ​​in the table? Thank you

Answer 1

This is a bit complicated, but you can do it in one expression update:

update table t left outer join
       table tc
       on t.a = tc.a and t.b = tc.b and tc.c is not null left outer join
       table tb
       on t.a = tb.a and t.c = tb.c and tb.b is not null
    set t.c = coalesce(t.c, tc.c),
        t.b = coalesce(t.b, tb.b);

. . , .

, , select:

select *
from   table t left outer join
       table tc
       on t.a = tc.a and t.b = tc.b and tc.c is not null left outer join
       table tb
       on t.a = tb.a and t.c = tb.c and tb.b is not null;



set c = (case when c is null
              then (select c from