I have 2 tables with the same structure but different content:
table_a:
+----+------------+
| id | date |
+----+------------+
| 1 | 2023-02-02 |
| 2 | 2023-10-12 |
| 3 | 2023-02-04 |
+----+------------+
table_b:
+----+------------+
| id | date |
+----+------------+
| 3 | 2022-11-11 |
+----+------------+
I’m trying to make a query that selects records from both tables, and on duplicate key (in this case 3), select the table_b one.
So the result would be:
query:
+----+------------+
| id | date |
+----+------------+
| 1 | 2023-02-02 |
| 2 | 2023-10-12 |
| 3 | 2022-11-11 |
+----+------------+
I tried
SELECT * from table_a a left join table_b b on a.id = b.id;
+----+------------+------+------------+
| id | date | id | date |
+----+------------+------+------------+
| 1 | 2023-02-02 | NULL | NULL |
| 2 | 2023-10-12 | NULL | NULL |
| 3 | 2023-02-04 | 3 | 2023-11-11 |
+----+------------+------+------------+
Is there a way to overwrite the table_a record with the table_b one to get everything in 2 columns instead of 4?
>Solution :
This can be achieved by simply applying UNION between all records from table_a that do not have a matching id in table_b. Something like:
SELECT *
FROM table_a
WHERE id NOT IN (SELECT id FROM table_b)
UNION
SELECT *
FROM table_b