Update or insert records to the same table based on a similar table but with conditions

Question

I'm tryin to update or insert records on the same table based on a similar table using Merge but based on the count of records in the source table.

I have 2 tables say one is POV [pov, dc, item](the table where I want to insert/ update), the other table is KLN [pov, wh , item].

The source table for the merge is by joining these 2 tables, POV and KLN based on [pov, wh, item] so the excess records for the same [pov, item] that are present in the second table KLN should be inserted/ updated based on the count of records that are generated in the join with same [pov, item].

• If only one record is generated for the same [pov, item] combination I want to update my target table.
• If multiple records are generated I want to insert these records into the table.

I'm unable to use GROUP BY since I need the value of [wh] as well [pov, wh, item] all the records have distinct [wh] for the same [pov, wh] based on the count of records for the same I want to update my table.

Here's what I wanna do:

merge into POV {target} using
 (select distinct POV.pov, POV.item, **KLN.WH**
       ,count(/*of the number of [WH] generated with same pov and item*/)
  from POV, KLN 
   where POV.pov = KLN.pov
   and POV.item = kln.item ) as { source }
on (
    target.pov = source.pov
    and target.item = source.item
    and source.count = 1
    )
 when matched 
    then update
       set tgt.pov = **source.WH**
 when not matched
     then insert

I can take care of this. I just need a solution to help me get the count as well as distinct WH from KLN table.

This is my first time asking a question, please don't mind. I'll be really thankful for any help.

Answer 1

If I understand your question well then a windowed function could help you. You could try the following code:

MERGE INTO POV target
USING (
  SELECT DISTINCT POV.pov, POV.item, KLN.wh,
    COUNT(KLN.wh) OVER (PARTITION BY POV.pov, POV.item) AS cnt
  FROM POV, KLN
  WHERE POV.pov = KLN.pov
    AND POV.item = KLN.item
) source ON (
  target.pov = source.pov
  AND target.item = source.item
  AND source.cnt = 1
)
WHEN MATCHED THEN
  UPDATE SET target.dc = source.wh
WHEN NOT MATCHED THEN
  INSERT (pov, dc, item)
  VALUES (source.pov, source.wh, source.item);

The PARTITION BY clause sets the range of records that will be used for each "GROUP" within the OVER clause.

In your example, cnt will return the number of KLN.wh within that POV.pov, POV.item for every record (you still return every record in the emp table). So you can use the count in the merge and also you have KLN.wh record in the update part.

Here is a demo for your case: https://dbfiddle.uk/codWTkoI

NOTE! You wrote "set tgt.pov = source.WH" but you can't use the same column in the ON an the UPDATE part so I overwrite it.