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.