CODEWITHSUNDEEP
sqljointeradata
lolo
lolo

Reputation: 646

Teradata - Joins between two tables - Mantain values from first table

suppose I have the next 2 tables. In the first one, I have the most credible information. The second one has less reliable information. The idea is to create a third table that keeps all the information from the first one and adds only those fields that the first one doesn't have.

Initial table

       user    rank   id_best
        1       1       'A'
        1       2       'P'
        1       3       'E' 
        2       1       'Q'
        2       2       'E'
        3       1       'P'
        3       2       'O'
        3       3       'C'

Intermediate table

       user    rank   id_best
        1       1       'B'
        1       2       'D'
        1       3       'R'
        1       4       'Q' 
        1       5       'W'  
        2       1       'T'
        2       2       'Y'
        2       3       'S'
        2       4       'Q'
        2       5       'E'
        3       1       'Y'
        3       2       'E'
        3       3       'S'
        3       4       'A'
        3       5       'T'

Final table

       user    rank   id_best
        1       1       'A'
        1       2       'P'
        1       3       'E'
        1       4       'Q' 
        1       5       'W'  
        2       1       'Q'
        2       2       'E'
        2       3       'S'
        2       4       'Q'
        2       5       'E'
        3       1       'P'
        3       2       'O'
        3       3       'C'
        3       4       'A'
        3       5       'T'

Upvotes: 0

Views: 44

Answers (2)

dnoeth
dnoeth

Reputation: 60513

#1 Another solution using OLAP-functions:

select *
from
 (
   select 1 as flag, o.*
   from original o
   union all
   select 2, i.*
   from intermediate i
 ) as dt
qualify
   row_number()
   over (partition by user, rank
         order by flag) = 1 --prefer table 1 over table 2

#2 If user/rank is unique in at least one tables you can also do a full outer join:

select 
  coalesce(o.user, i.user),
  coalesce(o.rank, i.rank),
  coalesce(o.id_best, i.id_best)
from original o
full join intermediate i
  on o.user = i.user
 and o.rank = i.rank

This will preform extremely well if user/rank is the Primary Index in both tables. Also works when both tables got different columns.

#3 If you actually want to create a 3rd table you might also copy the initial table and then MERGE intermediate.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This is a prioritization query. One method uses union all logic like this:

select o.*
from original o
union all
select i.*
from intermediate i
where not exists (select 1
                  from original o
                  where o.user = i.user and o.rank = i.rank
                 );

Upvotes: 1

Related Questions