Need help on build sql

Hi
I have two tables.
1) Venn
2) OL

i need to update a column ( medtronic_billed) in the venn table where the mfg_item_id matching.

For example..

in the Venn table when we filter based on the mapped ='OL' and mapped_final = OL and tthen ake the mfg_item_id  value  and query in the OL table. there might be multiple records in the OL table.
in that case add the vocher amount and update that vocher amount in to venn table medtronic_billed column

Please check attached spread sheets

Please let me know if any questions
ol.xlsx
venn.xlsx
PRAVEEN TAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
We need to use the merge statement for this. Alternatively we can also use UPDATE statement. I prefer the MERGE Statement.

Please use this tested solution-

MERGE INTO venn n
USING 
( SELECT MFG_ITEM_ID , SUM(VCHR_AMOUNT) VCHR_AMOUNT FROM ol1 
GROUP BY MFG_ITEM_ID  ) o
ON (o.MFG_ITEM_ID = n.MFG_ITEM_ID 
AND n.mapped ='OL' and n.mapped_final = 'OL' )
WHEN MATCHED THEN 
    UPDATE SET n.MEDTRONIC_BILLED = n.MEDTRONIC_BILLED + o.VCHR_AMOUNT;

Open in new window


OUTPUT

--

mfg_item_id          medtronic_billed                        pot_savings_by_medtronic                mapped mapped_final
-------------------- --------------------------------------- --------------------------------------- ------ ------------
0103L14              257,22                                  0                                       OL     OL
0103SI14             0                                       0                                       OL     OL
0103SI16             0                                       0                                       OL     OL
0170SI12             0                                       0                                       OL     OL


--

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The classic way is
update VENN set medtronic_billed = medtronic_billed + (select sum(vchr_amount) from OL where mfg_item_id = VENN.mfg_item_id)
where mapped ='OL' and mapped_final = 'OL' 
and exists (select * from where mfg_item_id = VENN.mfg_item_id)

Open in new window

0
 
PRAVEEN TAuthor Commented:
Working
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.