Updating a column using values from a Lookup table

Question

I have a list of products in table, products. This may contain over 5 million records.

prod_code   prod_region     prod_desc       prod_type
------------------------------------------------------
1001        R2              r2 asdasa
1001        R1              r1 erfffv
1002        R4              r4 vfdser
1003        R2              r2 sdfdfv

prod_code and prod_region are not nullable.

I need to update the prod_type in this table, selecting from another lookup table, product_type.

prod_type   prod_code   prod_region
-----------------------------------
1           1001 
2           1002 
2           1003 
3           1001        R1

In this table, prod_region can be null. If it is null it should be interpreted as Anything.

So my updated products table should be,

prod_code   prod_region     prod_desc       prod_type
------------------------------------------------------
1001        R2              r2 asdasa       1       
1001        R1              r1 erfffv       3
1002        R4              r4 vfdser       2
1003        R2              r2 sdfdfv       2

Explanation of the desired output.

For prod_code = 1001 there are two entires in product_type. prod_type = 3 for specific prod_region 'R1' and prod_type = 1 for rest of the regions. So, the first two records in products should get 1 and 3 respectively.
For prod_code 1002, 1003 there is no prod_region specified in product_type table. So third and fourth records are assigned prod_type = 2 irrespective of the prod_region.

The following merge statement fails due to ORA-30926: unable to get a stable set of rows in the source tables in Oracle or Failure 7547 Target row updated by multiple source rows. in Teradata.

merge into products
using product_type
on (products.prod_code = product_type.prod_code
    and products.prod_region = coalesce(product_type.prod_region,products.prod_region)
    )
when matched then update
set products.prod_type = product_type.prod_type;

Looking for standard SQL or Teradata specific answer.

lc. · Answer 1 · 2014-07-18T06:31:54.460

How about something like this:

update products
set prod_type = (
    select T.prod_type
    from product_type T
    where T.prod_code = products.prod_code
    and (
        T.prod_region = product.prod_region
        or (
            T.prod_region is null 
            and not exists (
                select 1 
                from product_type T2 
                where T2.prod_code = products.prod_code
                and T2.prod_region = product.prod_region
            )
        )
    )
)

Although one may question the reason for de-normalizing your data like this.

score 1 · Accepted Answer · answered Jul 18 '14 at 06:46

Instead of one complicated statement you might split it in two simple MERGEs:

merge into products
using product_type
   on products.prod_code = product_type.prod_code
  and product_type.prod_region is null   
when matched then update
set prod_type = product_type.prod_type;

merge into products
using product_type
   on products.prod_code = product_type.prod_code
  and products.prod_region = product_type.prod_region
when matched then update
set prod_type = product_type.prod_type;

Updating a column using values from a Lookup table

2 Answers2