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