Problem update - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem update

Author

Topic

lloydnicholson
Starting Member

5 Posts

Posted - 2002-10-30 : 10:48:53

I have two tables tblleaderboard and tblprizes.
tblprizes.

id int
prize varchar
url varchar

tblleaderboard

id int
total decimal
email varchar
name varchar
prize varchar

The problem I have is I need to update tblleaderboard.prize with tblprize.prize based on the highest tblleaderboard.total and tblprizes.id.
Basically there are 200 prizes in tblprizes. The top prize has an tblprizes.id of 1 and the second prize is 2 and so on. SO the tblprizes.id = 1 has to update tblleaderboard.prize where the total is the highest And then loop through tblleaderboard updating the other 199 prizes into the corresponding prize field based on the total field.

The tblleaderboard changes every 15 seconds so the method has to be fast!
This is driving me mad!

Cheers,

Lloyd Nicholson-Taylor

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-02 : 11:01:00

I haven't tried this but...

An intermediate table that can join the two tables properly before updating the prizes would be very fast, without using any slow cursors or loops.

-- a temp table or table variable can be used if you don't want a permanent table

DECLARE @tbl TABLE -- go with the table variable
(LeadID INT, PrizID int identity(1,1))

-- PrizID will populate automatically, creating a table that can be joined with tblprizes on PrizID =id

INSERT INTO @tbl (LeadID) -- Gives a table of LeadIDs in order of Total
SELECT ID
FROM tblleaderboard
ORDER BY Total DESC

-- Now Update the prizes

UPDATE tblleaderboard
SET prize = tblprizes.prize
FROM @tbl INNER JOIN tblprizes on tblprizes.ID=@tbl.PrizID
WHERE tblleaderboard.ID=@tbl.LeadID

Watch out. I haven't tried this, so I'm sure there are bugs to be found.

Sam

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-11-02 : 17:37:02

It helps if you post reall DDL instead of a personal psuedo-code. let's re-do the tables and get rid of the silly "tbl-" and vague columns names:

CREATE TABLE Prizes
(place INTEGER NOT NULL PRIMARY KEY,
prize VARCHAR(50) NOT NULL,
url VARCHAR(250) NOT NULL); -- what is the upper limit for a url?

CREATE TABLE Leaderboard
(total DECIMAL NOT NULL (12,4),
email VARCHAR(250) NOT NULL, -- upper limit for email?
name VARCHAR(35) NOT NULL); -- USPS standard

>> The problem I have is I need to update Leaderboard.prize with Prizes.prize based on the highest Leaderboard.total and Prizes.id.
Basically there are 200 prizes ... The tblleaderboard changes every 15 seconds so the method has to be fast! <<

Use a VIEW that will always be right when you invoke it.

SELECT P1.place, L1.name, L1.email, P1.prize, P1.url
FROM Prizes AS P1,
Leaderboard AS L1
WHERE L1.place
= (SELECT COUNT(DISTINCT L2.total)
FROM Leaderboard AS L2,
Leaderboard AS L3
WHERE L2.total <= L3.total);

You did not say how to handle ties, so I made a guess. I am also assuming that the prizes are numbered from 1 to 200. The n-th place prize goes to the guy(s) with (n) lower or equal totals in the Leaderboard.

Since Prizes is small, it should fit into main storage and this shluld run pretty fast.

--CELKO--
Joe Celko, SQL Guru

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-03 : 08:58:03

Using a view (or select) to get the results is more appealing. I wish I had thought of it myself. The method to choose the order was new to me, so I had to run it though QA to figure it out.

There were some minor problems. Here's the query I had in QA when I finished testing.

SELECT P1.place, L1.name, L1.email, P1.prize, P1.url, L1.Total
FROM Prizes AS P1,
Leaderboard AS L1
WHERE P1.place
= (SELECT COUNT(DISTINCT L2.total)
FROM Leaderboard AS L2
WHERE L2.total >= L1.total)
ORDER BY P1.Place

(Could also be ORDER BY L1.Total DESC)

Results
-------
1,Bobby 2 Jones, Email2@hotmail.com ,Prize Number 1,This us URL 1,980.7202
2,Bobby 7 Jones, Email7@hotmail.com ,Prize Number 2,This us URL 2,889.0301
3,Bobby 1 Jones, Email1@hotmail.com ,Prize Number 3,This us URL 3,785.9825
4,Bobby 6 Jones, Email6@hotmail.com ,Prize Number 4,This us URL 4,693.2785
5,Bobby 4 Jones, Email4@hotmail.com ,Prize Number 5,This us URL 5,493.0621
6,Bobby 3 Jones, Email3@hotmail.com ,Prize Number 6,This us URL 6,485.7393
7,Bobby 5 Jones, Email5@hotmail.com ,Prize Number 7,This us URL 7,52.4670

Sam

(Revised to fix the email hyperlinks leaking to adjoining columns in results)

Edited by - SamC on 11/03/2002 08:59:13

lloydnicholson
Starting Member

5 Posts

Posted - 2002-11-04 : 06:43:44

Thank you for your help guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources