x

Best way to update data in a table

I have the following data in a sql server 2005 table:

team    score  week	win	loss
team1   100    1		
team2   120    1		
team3   130    1		
team4   80     1		
team5   90     1

I want to update the win & loss columns so that each team has results compared to all other scores that week, so team1 would show 2 wins, 2 losses, team2 would have 3 wins, 1 loss, etc. Ever week the scores would be inputted and I want the wins and losses calculated.

How do I go about this? It is a small database.

more ▼

asked Dec 04, 2009 at 07:20 PM in Default

user-697 gravatar image

user-697
21 2 2 2

I think you need some more information about what will be entered. How do you go from 0-0 to 2-2?
Dec 04, 2009 at 09:41 PM Steve Jones - Editor ♦♦
also, how do you change weeks? Update the column or add a new row?
Dec 04, 2009 at 09:42 PM Steve Jones - Editor ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

(I believe this will do what you want as long as there are no ties.)
I was originally going to use RANK() but decided against it. After seeing Jonathan's answer, I reconsidered the use of RANK(). I have edited my answer back to using RANK() and added the ability to include ties. If ties are not possible, you can just remove that column from the update.

IF OBJECT_ID('dbo.UpdateWeekWinsAndLosses') is not null
    DROP PROCEDURE dbo.UpdateWeekWinsAndLosses
GO
CREATE PROCEDURE dbo.UpdateWeekWinsAndLosses
    @week smallint
AS
BEGIN
    SET NOCOUNT ON;

    WITH WinLoss (team, win, loss, tie)
    AS
    (
    	SELECT tr.team,
    		RANK() OVER (PARTITION BY tr.week ORDER BY tr.score ASC) - 1 AS win,
    		RANK() OVER (PARTITION BY tr.week ORDER BY tr.score DESC) - 1 AS loss,
    		COUNT(*) OVER (PARTITION BY tr.week) -
    			(RANK() OVER (PARTITION BY tr.week ORDER BY tr.score ASC) - 1) -
    			(RANK() OVER (PARTITION BY tr.week ORDER BY tr.score DESC) - 1) - 1 AS tie
    	FROM TeamRecords tr
    	WHERE tr.week = @week
    )
    UPDATE TeamRecords
    SET win = wl.win, loss = wl.loss, tie = wl.tie
    FROM TeamRecords tr
    	INNER JOIN WinLoss wl
    	ON wl.team = tr.team
    WHERE week = @week
END
GO

Each week, run the procedure for that week #.

EXEC dbo.UpdateWeekWinsAndLosses 1 
more ▼

answered Dec 05, 2009 at 04:59 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Thanks for the replies. Both worked well, but I did not take ties into consideration when I posted my question. Option 2 works best for my needs. Thanks for the help.
Dec 07, 2009 at 11:49 AM user-697
(comments are locked)
10|1200 characters needed characters left

A pair of reverse sorted RANK() functions should do the trick:

declare @scores table (team int, score int, week int, win int, loss int) insert into @scores values (1, 100, 1, null, null)
insert into @scores values (2, 120, 1, null, null) insert into @scores values (3, 130, 1, null, null) insert into @scores values (4, 80, 1, null, null) insert into @scores values (5, 90, 1, null, null) insert into @scores values (1, 80, 2, null, null)
insert into @scores values (2, 110, 2, null, null) insert into @scores values (3, 90, 2, null, null) insert into @scores values (4, 120, 2, null, null) insert into @scores values (5, 100, 2, null, null)

select team, score, week, rank() over (partition by week order by score asc) - 1 as win, rank() over (partition by week order by score desc) - 1 as loss from @scores order by week, team
more ▼

answered Dec 06, 2009 at 01:10 AM

Jonathan Kehayias gravatar image

Jonathan Kehayias
283 1 1 2

Nice solution. I was concerned this wouldn't work for ties, but it's actually pretty good. If ties are possible, you could just add a column. I edited my answer to show an example.
Dec 06, 2009 at 07:34 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1944
x129

asked: Dec 04, 2009 at 07:20 PM

Seen: 1925 times

Last Updated: Dec 04, 2009 at 07:33 PM