update statement main table based on records in temp all types of an order when a product present in temp table

Question

I have to update status to all items of an product type for each order_id if even a single item of a product type of an order is present in temp table . Below are 3 tables

product table

temp table

main table

I'm updating status with below statement in main table for all items of a product_type for an order_id if even a single item of a product_type of an order is present in temp table

update main_table t 
set status='inactive' 
from (  select i.order_id,
               pt.type 
        from temp_table i 
        inner join product_table pt  on i.product_id=pt.product_id 
        where i.key is not null ) as a1 
                
inner join (  select mt.order_id,
                     mt.key,pt1.type 
              from main_table mt 
              inner join product_table pt1  on mt.product_id=pt1.product_id) a2 
   on a1.order_id=a2.order_id 
   and a1.type=a2.type 
     
where t.key=a2.key

expected output would be

main table has 10 million records and temp table may have 2K records. I think above query is complex as it has 2 subquery and joins.

Is there a way can we rewrite query to improve query execution ?

Answer 1

Your query is a bit too complex to simplify too much further due to needing to join product more than once to obtain all product_id's of the same product_type. However, we can rework your a2 query into the main UPDATE statement, thereby eliminating at least 1 join against main.

update  main mt
set prodstatus = 'inactive'
from product p 
inner join (select i.order_id,
                   pt.prodtype 
            from temp i 
            inner join product pt on i.product_id=pt.product_id 
            where i.pk is not null) as tempProds
  on p.prodtype = tempProds.prodType
where mt.product_id = p.product_id  
  and mt.order_id = tempProds.order_id

This fiddle shows you the execution advantage if you run our UPDATE statements separately using the EXPLAIN option.