SQL Help - table update?
-
Hi, I'm really new to programming SQL and I'm trying to learn the correct way to do things.
Here is what I'm trying to do....
I have a table named tblMP3Player which has a column named Skin which contains a number that relates to the identity column named ID in the tblMP3Skins table.
The tblMP3Skins table also has Rank, Rating, and Used_By column.
I need to some how update the Skins table so that the Used_By column has a count of how many players are using that skin. I then need to update the Rank column for each skin so that the most used skin is ranked as 1, second most used at 2, and so on. If there are skins that have the same value in Used_By I would like to use the skins Rating to decide which will get the higher rank.
I want to update this info daily, right now my website is small (just about 900 members) but I'm trying to be thinking of the future because looping through all these tables multiple times and updating one row at a time (which is what I'm doing now) is probably not the best way if there were 50,000 members.
Thanks in advance for any input you offer.
-
Hi Chris,
You'd need something like this:
update tblMP3Skins
set Used_By = u.Used_By
from tblMP3Skins s
join
(
select p.Skin, count(*) as Used_By
from tblMP3Skins s
join tblMP3Player p on p.Skin = s.Skin
group by p.Skin
) u
on u.Skin = s.Skin
Now a couple of points that you might want to consider. At first glance it doesn't seem that you need the Rank colum (although it might be necessary for other parts of your application - I don't know for sure).
You see, the Rank is derived from the most used skin so there's probably no need to store it, which incurs an overhead whenever you update these tables - as you've found out.
If you just want to get items out in order of "Rank" just run the following statement.
select * from tblmp3Skins order by Used_By desc, Rating Asc.
This ensures that the most used skin appears at the top when you're getting the data out. In the event that there are two skins with the same number of uses, the Rating Asc will ensure that the ones with the higher rating come out on top.
Along the same lines, The Used_By column in Skins is a derived or computed column and I would suggest that you don't need to store this value. The reason being that you're going to have to manually update (every night) this value in the mp3Skins table so the data is never real-time.
Instead, you could get this real-time value of Used_By in the following way.
select *
from tblMP3Skins s
join
(
select p.Skin, count(*) as Used_By
from tblMP3Skins s
join tblMP3Player p on p.Skin = s.Skin
group by p.Skin
) u
on u.Skin = s.Skin
This will always return a true real value and by not storing it in the database you remove the need to remember to perform some update at the end of the day.
I can see that you're thinking about the future and what things may be like when you have 50,000 members, which is good but SQL Server should be able to handle the above query without much problem even with 50,000 records.
If you do continue to plan on storing the Used_By data another consideration would be to implement a trigger on the mp3Players table where every insert/update will update the Used_By column in the mp3Skins.
As with everything there isn't a single best solution but I've tried to offer you a couple of alternatives, which might (or might not) be applicable to your situation.
Hope that helps,
-
Thanks a lot for all your help!
I tried your SQL to update the Used_by column and it worked great except it skips all rows for skins that are no longer being used so they still have a value other than zero.
I didn't know you could do this:
(
select p.Skin, count(*) as Used_By
from tblMP3Skins s
join tblMP3Player p on p.Skin = s.Skin
group by p.Skin
) u
That's going to help me a lot, just knowing I can do that.
I have been reading about triggers in a book I bought. They seem like they might be the way to go for keeping the Used_By column up to date. I'll have to read some more to figure out how to apply them to what I doing.
I would rather not have to store the rank at all but I have a page that shows only the skins that belong to a single user and that's the page I need the rank for because I will have to use "where User_ID = X" so I won't be getting all the rows in order by Used_By.
I hope I'm not pushing my luck here but I have another question maybe you can help me with.
On the page where users can browse all the skins I would like to sort them by Used_By, User_ID, or Rating which ever the user selects (that's the easy part) The problem I'm seeing myself facing is once I order by one of those fields and pull enough records for the first page of skins to display, how can I tell SQL server to get the next page worth of records after the ones I got for the first page? I know I can use TOP to get the first page, but for some reason I'm guessing there is not a MIDDLE lol
although maybe there should be. -
Chris,
Glad that I was able to help and hope you can work things out from there.
Regarding your second problem, ideally, you'd want to handle this kind of thing on the middle-tier. I'm guessing that you're displaying this for use on a web page. If you're using ASP or ASP.NET then you can manage paging of resultsets with their in-built controls.
If you had to do this through T-SQL then it's possibly but would involve dynamic SQL - either on the SQL Server or by building a dynamic SQL string in your code. Neither of which are ideal for security reason.
Anyway, if you wanted the records 21-30 you can look for the top 10 records, excluding the previous 20 records. If you want the records 31-40 you can look for the top 10 records, excluding the previous 30 records... and so on.
Here's an example piece of T-SQL, which gets records 21-30 (or page 3 assuming that you're display 10 records on each page).
select top 10 *
from mytable
where mytableid not in
(select top 20 mytableid from mytable order by ) --returns page 3
order by
select top 10 *
from mytable
where mytableid not in
(select top 30 mytableid from mytable order by ) --returns page 4
order by
To do this dynamically you would have to pass through a page number like so.
declare @page_no int
declare @excluded_rows int
declare @CmdStr as varchar(1000)
set @page_no = 3
set @excluded_rows = (@page_no - 1) * 10 --assumes 10 records per page
set @CmdStr = 'select top 10 *
from mytable
where mytableid not in
(select top ' + @excluded_rows + ' mytableid from mytable order by )
order by '
exec sp_executesql @CmdStr
Hope that helps,
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply