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
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);
a and table b has 40 columns? Or does only one of the table have 40 columns? If only one table has 40 columns, which table has 40 columns? Do you want only column f2's missing data populated or do you want all other 38 column's missing data populated also?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.
NULLor are they empty strings? They're not the same thing.