Adding values from different tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

Adding values from different tables

Author

Topic

damdeok
Starting Member

39 Posts

Posted - 2012-11-10 : 14:26:52

Friends, I have these tables:

Contestant Table:
Winner
Peter

Group Table:
Id Name Score Union
1 Bryan 3 77
2 Mary 1 20
3 Peter 5 77
4 Joseph 2 25
5 John 6 77

I want to give additional score of 5 to Peter on Group Table. So, I came up with this query.

UPDATE Group 
SET Score = Score+5
FROM Contestant, Group 
WHERE Contestant.Winner = Group.Name

Now, I want also to give additional score of 5 to the same Union as Peter which is 77.

How can I integrate it as one query to my existing query?

Mike Jackson
Starting Member

37 Posts

Posted - 2012-11-12 : 10:46:40

You can't integrate it with the current query because your join (Contestant.Winner = Group.Name) exclueds all other records except Peter.

but you could run 2 queries, something like

UPDATE Group
SET Score = Score + 5
FROM Group
where Union=(
select g.union
FROM Group g
join Contestant c
on C.Winner = G.Name
)

but you would have to run this one first. Otherwise your query would change the [union] field and it would no longer be 77 and there would be no matches.

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources