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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.