Update Problem - 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)

Update Problem

Author

Topic

lloydnicholson
Starting Member

5 Posts

Posted - 2002-09-23 : 07:23:24

I have a problem...
I have two tables - tblleaderboard
ID int
Name varchar
Date datetime
Total decimal
Prize varchar

And tblprizes

ID int
PRIZE varchar
PRIZE_DESC varchar
IMAGE_URL varchar

tblleaderboard changes every 1-2 seconds because new values are entered.

What I want to achieve is to UPDATE tblleaderboard.prize from tblprizes.prize Ordered by ID (the top prize has an id of one and so on) starting with the highest total in tblleaderboard and looping until the prize table has been inserted to into the corresponding row on the tblleaderboard.

The problem is there is nothing to relate the two tables because the row with the highest score changes from second to second. This update has to be a stored proccedure!

I hope I have explained myself fully.

Many thanks,

Lloyd

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-23 : 07:32:04

If you want the leaderboard to be in step with prizes then you will have to do it in a trigger.
something like.

create trigger tr on tblprizes for insert, update, delete
as
if exists(select * from inserted)
update tblleaderboard
set prize = i.prize
from tblleaderboard lb, inserted i
where lb.id = i.id

else

update tblleaderboard
set prize = null
from tblleaderboard lb, deleted d
where lb.id = d.id
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources