Best way to update data in a table

Question

question

user-697 asked Dec 4, '09

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.

sql-server-2005 update

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steve Jones - Editor ♦♦ commented · Dec 04, 2009 at 09:41 PM

I think you need some more information about what will be entered. How do you go from 0-0 to 2-2?

0 ·

Steve Jones - Editor ♦♦ commented · Dec 04, 2009 at 09:42 PM

also, how do you change weeks? Update the column or add a new row?

0 ·

Answer 1 · 2009-12-05T04:59:33Z

Tom Staab answered Dec 5, '09

(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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-12-06T01:10:51Z

Jonathan Kehayias answered Dec 6, '09

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question