Skip to content
Advertisement

Merge tables with unequal number of columns without returning duplicate rows

I have two tables, one is called original_table and the other is table_with_additional_column.

table_with_additional_column is a subset of original_table but with an additional column called sequenceNr. I want to merge the two tables such that I can get all rows from original_table with sequenceNr set to NULL where the rest of the column values don´t match the ones in table_with_additional_column. I would like to replace the rows in original_table instead of just inserting the rows from table_with_additional_column

Here´s an example:

original_table

column_1 column_2 column_3
aaa bbb ggg
ccc ddd hhh
eee fff iii

table_with_additional_column

column_1 column_2 column_3 sequencenr
aaa bbb ggg 1
ccc ddd hhh 2

What I want as result of merge:

column_1 column_2 column_3 sequencenr
aaa bbb ggg 1
ccc ddd hhh 2
eee fff iii NULL

Here´s my query so far:

SELECT 
  column_1, column_2, column_3, sequenceNr

FROM (
    SELECT 
        table_A.*, 
        table_B.sequenceNr
    FROM table_A, table_B
) AS table_with_additional_column

RIGHT JOIN original_table ON original_table.column_1 = table_with_additional_column.column_1
AND original_table.column_2 = table_with_additional_column.column_2 
AND original_table.column_3 = table_with_additional_column.column_3

I would appreciate some help with the SQL query regarding how to merge the two tables. Thanks in advance.

Advertisement

Answer

You can use left join for this:

select ot.*, twac.sequencenr
from original_table ot left join
     table_with_additional_column twac
     on ot.col1 = twac.col1 and
        ot.col2 = twac.col2 and
        ot.col3 = twac.col3;

Your description pretty much explains exactly what left join does.

9 People found this is helpful
Advertisement