Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-07-31 : 07:12:11

Hey Guys

I am hoping you will be able to help me. i am trying to do multiple column updates at a given time

This is my first update which works fine,

--Updating Msip Monthly Gross Sales with Omnipay Monthly Gross Sales --
UPDATE ma
SET [Monthly Gross] = ma.[Monthly Gross]+ Omni.MonthlySales
FROM #mason AS ma
INNER JOIN #Omnipay AS Omni ON Omni.ParentID = ma.[Parent ID(MID)]

however i also want to update the following

UPDATE ma
SET [Monthly Net] = ma.[Monthly Net]+ Omni.NetSales
FROM #mason AS ma
INNER JOIN #Omnipay AS Omni ON Omni.ParentID = ma.[Parent ID(MID)]

any solutions ?

Would appreciate any feedback available

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-31 : 07:19:29

UPDATE ma
SET
[Monthly Gross] = ma.[Monthly Gross]+ Omni.MonthlySales,
[Monthly Net] = ma.[Monthly Net]+ Omni.NetSales
FROM #mason AS ma
INNER JOIN #Omnipay AS Omni ON Omni.ParentID = ma.[Parent ID(MID)]

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-07-31 : 07:29:37

Webfred you beauty :)

this is the final outcomer

UPDATE ma
SET
[Monthly Gross] = ma.[Monthly Gross]+ Omni.MonthlySales,
[Monthly Net] = ma.[Monthly Net]+ Omni.NetSales,
GrossTrans = ma.GrossTrans + Omni.Trans,
NetTrans = ma.NetTrans + Omni.NetTrans,
[PY Total Sales] = ma.[PY Total Sales] + omni.[OPY Total Sales],
[MSC R12] = ma.[MSC R12] + omni.R12MSC,
[Rolling 12 YTD] = ma.[Rolling 12 YTD] + Omni.R12Sales
FROM #mason AS ma
INNER JOIN #Omnipay AS Omni ON Omni.ParentID = ma.[Parent ID(MID)]
select * from #mason
where [Parent ID(MID)] = '878027321883'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 07:44:32

Is the relationship between the tables 1 to 1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-31 : 07:54:05

Beauty hahaha :)

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources