CODEWITHSUNDEEP
mysql
xaxado2791
xaxado2791

Reputation: 1

mysql - how to join two tables and only add values from the second table if there are nulls in the first table?

table A that has X and Y fields. some Y fields are blank. table B has X and Z fields. how do I join A and B such that only the blank Y fields get populated with Z values and none of the existing Y fields are overwritten without making a new table and unioning it?

A
X1    Y1
X2    
X3    Y2

B
X1    Z1
X2    Z2
X3

A join B
X1    Y1
X2    Z2
X3    Y2

Upvotes: 0

Views: 42

Answers (2)

PaulProgrammer
PaulProgrammer

Reputation: 17620

You could do two different SELECT statements with a UNION.

SELECT v1, v2 FROM A WHERE v2 is NOT NULL
UNION ALL
SELECT B.v1, B.v2 FROM A, B 
  WHERE B.v1 = A.v1 
  AND A.v2 IS NULL
  AND B.v2 IS NOT NULL

The first one takes care of the primary case where the value exists in A. The second takes care of when the value is not in A, but is in B.

This does use a UNION but does not create a new table.

http://sqlfiddle.com/#!9/65c497/2

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37039

You can use a query like this to display data from table b if table a is missing some info like so:

select a.f1, coalesce(a.f2, b.f2) as f2 from a left join b on a.f1 = b.f1;
+------+------+
| f1   | f2   |
+------+------+
| X1   | Y1   |
| X2   | Z2   |
| X3   | Y2   |
+------+------+

Note that this will not change your table. It'll just output information as you desire.

If you want to actually update table a and populate missing values with those from table b, you can do something like this:

update a inner join b on a.f1 = b.f1
set a.f2 = b.f2
where a.f2 is null and b.f2 is not null;

Then, your table a will look like this:

select * from a;
+------+------+
| f1   | f2   |
+------+------+
| X1   | Y1   |
| X2   | Z2   |
| X3   | Y2   |
+------+------+

Example tables I used are:

create table a (f1 char(2), f2 char(2));
create table b (f1 char(2), f2 char(2));
insert into a values ('X1', 'Y1'), ('X2', null), ('X3', 'Y2');
insert into b values ('X1', 'Z1'), ('X2', 'Z2'), ('X3', null);

Upvotes: 1

Related Questions