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
 Sum old data and new data in Update Query

Author  Topic 

AnalystRU9
Starting Member

2 Posts

Posted - 2014-07-22 : 09:19:02
Hi

New to SQL and this forum, apologies if this question has been asked and answered elsewhere.

I have created a table in a database for a football that I want to update as the season progresses. Is there any way I can add the new data to the old data in my columns through SQL rather than searching for the old data, doing the maths in my head and doing a simple update query? I know it doesn't save much extra time but I can get awful lazy when it comes to extra maths!

Just to help, an example would be:

Player Minutes
Adam 287

Adam has played 287 minutes prior to the most recent match in which he played 67 more. I would like to know if its possible to do an update where I can just simply add 67 onto the existing numbers with one query rather than find Adam's minutes, use a calculator, do a table update.

Thanks in advance, sorry for the lengthy writing!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-22 : 10:11:04
[code]UPDATE YourTable SET Minutes = Minutes + 67 WHERE Player = 'Adam';[/code]
Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-22 : 10:12:50
UPDATE MyFootballTable
SET PlayerMinutes = PlayerMinutes + 67
WHERE Player = 'Adam'

-- You are trying to add to an existing value via an update. This will work. I used the Player = 'Adam'
as the target in the where clause, but you would probably have a unique key identifying the player that would be used instead: Otherwise you would be updating all Adams.
Go to Top of Page

AnalystRU9
Starting Member

2 Posts

Posted - 2014-07-25 : 07:15:27
I knew it would be simple enough but just couldn't figure it out. Thank you both for your help.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-25 : 09:33:47
With SQL Server 2008R2, you can also use this syntax

UPDATE dbo.MyFootballTable
SET PlayerMinutes += 67
WHERE Player = 'Adam';




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -