0

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?

3
  • How do you decide to apply n before m or vice versa?
    – Rick James
    Jan 15, 2021 at 18:10
  • 1
    In this case the value in table A is not modified and is still X. No. UPDATE: Each matching row is updated once, even if it matches the conditions multiple times - but it is not defined what matching value will be used. So maybe value will stay unchanged, maybe it will change - noway to predict. can I modify my query to obtain this result? You must create a subquery which will select only one matched row from table B, and use it in UPDATE instead of the table.
    – Akina
    Jan 15, 2021 at 18:42
  • @Akina that's what I was missing. Thanks
    – Zucch
    Jan 18, 2021 at 7:36

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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