Join two tables but keep values of right table where ID field is the same

[dtate1959]

Hi all,

I feel I should be able figure this out, but, am drawing a blank.

I have two table which exist in QVDs.  Table 2 has overlapping values to Table 1. I want to use the values in Table 2 to overwrite the row values in Table 1 where the ID field has the same value resulting in a combined table with rows for both, but rows from table 2 are always preserved.

The resulting Table 3 will have all the rows and columns from both tables. Where  ID field matches in both tables, values for table 2 are kept rather than those in table 1: 

Table1:                                                                                 

ID	name	cost	startdate	finishdate
1	project1	123	1/1/2021	2/1/2021
2	project2	124	1/2/2021	2/2/2021
3	project3	125	1/3/2021	2/3/2021
4	project4	126	1/4/2021	2/4/2021
5	project5	127	1/5/2021	2/5/2021
6	project6	128	1/6/2021	2/6/2021
7	project7	129	1/7/2021	2/7/2021
8	project8	130	1/8/2021	2/8/2021
9	project9	131	1/9/2021	2/9/2021
10	project10	132	1/10/2021	2/10/2021
11	project11	133	1/11/2021	2/11/2021
12	project12	134	1/12/2021	2/12/2021
13	project13	135	1/13/2021	2/13/2021
14	project14	136	1/14/2021	2/14/2021
15	project15	137	1/15/2021	2/15/2021
16	project16	138	1/16/2021	2/16/2021
17	project17	139	1/17/2021	2/17/2021
18	project18	140	1/18/2021	2/18/2021
19	project19	141	1/19/2021	2/19/2021

Table 2:

ID	name	cost	startdate	customer
6	project6	150	1/1/2021	J Smith
7	project7	151	1/2/2021	R J Name
8	project8	152	1/3/2021	D T Person
9	project9	153	1/4/2021	J Smith
10	project10	154	1/5/2021	R J Name
11	project11	155	1/6/2021	J Smith
12	project12	156	1/7/2021	J Smith
13	project13	157	1/8/2021	J Smith
14	project14	158	1/9/2021	R J Name
20	Project20	159	1/10/2021	D T Person

Result Table 3:

ID	name	cost	startdate	customer	finishdate
1	project1	123	1/1/2021		2/1/2021
2	project2	124	1/2/2021		2/2/2021
3	project3	125	1/3/2021		2/3/2021
4	project4	126	1/4/2021		2/4/2021
5	project5	127	1/5/2021		2/5/2021
6	project6	150	1/1/2021	J Smith
7	project7	151	1/2/2021	R J Name
8	project8	152	1/3/2021	D T Person
9	project9	153	1/4/2021	J Smith
10	project10	154	1/5/2021	R J Name
11	project11	155	1/6/2021	J Smith
12	project12	156	1/7/2021	J Smith
13	project13	157	1/8/2021	J Smith
14	project14	158	1/9/2021	R J Name
15	project15	137	1/15/2021		2/15/2021
16	project16	138	1/16/2021		2/16/2021
17	project17	139	1/17/2021		2/17/2021
18	project18	140	1/18/2021		2/18/2021
19	project19	141	1/19/2021		2/19/2021
20	Project20	159	1/10/2021	D T Person

Attached is an Excel file with the sample data.

Thanks in advance for any and all help!!!

Dave T

[maxgro]

Try with not exists

Table3:
LOAD ID,
name,
cost,
startdate,
customer
FROM
[Table join example.xlsx]
(ooxml, embedded labels, table is [table 2]);

CONCATENATE (Table3)
LOAD ID,
name,
cost,
startdate,
finishdate
FROM
[Table join example.xlsx]
(ooxml, embedded labels, table is table1)
WHERE NOT EXISTS (ID);