Comparison between two table columns

[Sravan_Kuntumalla]

Hi All,

May I know the best way possible to compare a column values from a table with the list of values from another table and update the final table accordingly.

Let's say, I have a main table in the below format

table1:

ID	Col1	Col2
10	ABCD1	XYZ1
20	ABCD2	XYZ2
30	ABCD3
40	ABCD4	ABCD3
50	ABCD5
60	ABCD6	ABCD8
70	ABCD7	XXYZ
80	ABCD8
90	ABCD9	XXYZ
100	ABCD10

from the above table, I will be picking up distinct values of col2 into another table.

table2:

Column1

XXYZ

XYZ1

XYZ2

ABCD3

ABCD9

Now, I want to compare each value of col1 of table1 with each value of Column1 of table2. After comparing and apply calculations, the expected output should in the following format.

ID	Col1	Col2
10	ABCD1	XYZ1
20	ABCD2	XYZ2
30	ABCD3	ABCD3
40	ABCD4	ABCD3
50	ABCD5
60	ABCD6	ABCD8
70	ABCD7	XXYZ
80	ABCD8	ABCD8
90	ABCD9	XXYZ
100	ABCD10

Explanation: Initially, at ID=30, the value at Col2 of table1 was null, as the same col1 value is present in table2, the table1 col2 value should be modified to the same value as table1.col1.

Similarly, the same with ID=80.

Thanks in advance!

[Digvijay_Singh]

I think you can have table 2 as a mapping table having both columns same, then you can use applymap while creating the Column 2 of the final table when column 2 is null else you copy existing column 2 value as it is.