Search code examples
sqloracle-databasesql-updateteradatasql-merge

Updating a column using values from a Lookup table


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.

  1. 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.
  2. 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.


Solution

  • 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;