MySQL – Update Table from Join Returning Multiple Rows

MySQL

I am trying to merge information contained in 2 different archives but I will try to simplify my problem to its root cause.

I have 2 tables A and B and I want to update a column in A with values from a column of B if the value in B is not null, otherwise I have to preserve the value in A.
A and B are not direclty related but I have the information I need to identify which elements of A are also present in B.

To avoid perform all operations in a single and really complex query I've added a Foreign Key in table B where I store, if present, the key value of the corresponding element in A.

At this point my idea was to perform an update joining the tables:

UPDATE A
JOIN B ON B.A_FK = A.PK
SET A.INFO = IFNULL(B.INFO, A.INFO);

Unfortunately for me this doesn't work in all cases because in table B there can be more rows that refers to a single row of table A. For this scope lets assume that only one of them has a non null value.

Table A
--------------
| PK | value |
--------------
| 1  |   X   |
--------------

Table B
---------------------
| PK | value | A_FK |
---------------------
|  n |   Y   |  1   |
|  m |  null |  1   |
---------------------

In this case the value in table A is not modified and is still X.

I hoped that performing the update process it would have run the update 2 times for my row in A and that my result would have been the following but that's not the case.

Table A
--------------
| PK | value |
--------------
| 1  |   Y   |
--------------

I really would like to avoid resorting to a cursor to iterate over all rows, so can I modify my query to obtain this result?

Best Answer

Combine the rows of B (GROUP BY a_fk); in the process decide whether to keep Y or NULL.

Store the result of that into a temp table.

Then do the original UPDATE, but against the temp table.

So two statements, no looping:

CREATE TEMPORARY TABLE t
    SELECT ...
        GROUP BY a_fk;
UPDATE A JOIN t ON ...
    SET A... = t...;
Related Question