March 3, 2009 at 2:06 am
I need help to write a query for an update statement.
Table Structure and data follows
CREATE TABLE [dbo].[TblFeedbackData](
[USERID] [int] NULL,
[SCORE] [float] NULL,
[IsProcessed] [int] NULL
)
INSERT INTO TblFeedbackData VALUES(1,90,1)
INSERT INTO TblFeedbackData VALUES(2,35,1)
INSERT INTO TblFeedbackData VALUES(3,10,2)
INSERT INTO TblFeedbackData VALUES(4,62,2)
CREATE TABLE [dbo].[TblComputedData](
[USERID] [int] NULL,
[Rank] [int] NULL
)
INSERT INTO TblComputedData VALUES(1,NULL)
INSERT INTO TblComputedData VALUES(2,3)
INSERT INTO TblComputedData VALUES(3,8)
INSERT INTO TblComputedData VALUES(4,NULL)
CREATE TABLE [dbo].[TblMasterData](
[MinimumValue] [int] NULL,
[MaximumValue] [int] NULL,
[ReputationValue] [int] NULL
)
INSERT INTO [TblMasterData] VALUES(1,5,1)
INSERT INTO [TblMasterData] VALUES(6,15,2)
INSERT INTO [TblMasterData] VALUES(16,30,3)
INSERT INTO [TblMasterData] VALUES(31,50,4)
INSERT INTO [TblMasterData] VALUES(51,75,5)
INSERT INTO [TblMasterData] VALUES(76,105,6)
INSERT INTO [TblMasterData] VALUES(106,140,7)
INSERT INTO [TblMasterData] VALUES(141,180,8)
INSERT INTO [TblMasterData] VALUES(181,225,9)
INSERT INTO [TblMasterData] VALUES(226,275,10)
I need to write an Update Statement which will update the Rank column of TblComputedData from the ReputationValue of the TblMasterData Based on the SCORE values from TblFeedbackData for all Records where IsProcessed = 1
Note (ReputationValue will be picked as per the SCORE value, the range in which the score falls
Example: for a SCORE of 40, reputation value would be 4 because it ranges between 31 and 50
)
UserID of TblComputedData and TblFeedbackData are Primary Keys and can be brought into a join.
Any help would be greatly appreciated
Thanks
Zubair
March 3, 2009 at 2:48 am
Thanks for sharing Create and isnerts scripts
UPDATE C
SET C.rank = M.reputationvalue
FROM TblComputedData C INNER JOIN TblFeedbackData F
ON c.userId = F.UserId
INNER JOIN TblMasterData m
ON F.Score BETWEEN m.MinimumValue and M.MaximumValue
WHERE isProcessed =1
John Smith
March 3, 2009 at 3:26 am
UPDATE C
SET C.rank = M.reputationvalue
FROM TblComputedData C INNER JOIN TblFeedbackData F
ON c.userId = F.UserId
INNER JOIN TblMasterData m
ON F.Score BETWEEN m.MinimumValue and M.MaximumValue
WHERE isProcessed =1
There's a potential problem with this update statement.
If the TblFeedbackData.SCORE (float) column holds a non-integer value that is between the numeric ranges defined in the TblMasterData table (e.g. 15.5), then the row associated with this user will not be updated.
The following amendment fixes this issue, though you may wish to round the non-integer score to the nearest integer rather than truncating it, by adding 0.5 before converting to an int.
UPDATE C SET C.rank = M.ReputationValue
FROM TblComputedData C
INNER JOIN TblFeedbackData F
ON (C.userId = F.UserId)
INNER JOIN TblMasterData M
ON (CONVERT(int, F.Score) BETWEEN M.MinimumValue AND M.MaximumValue)
WHERE (F.IsProcessed = 1)
Also, what happens if the user's score is greater than the maximum MaximumValue in the TblMasterData table? Is this possible?
Because of the mismatch in datatypes between the TblFeedbackData.SCORE (float) and the TblMasterData.MinimumValue and TblMasterData.MaximumValue (int) columns, the join will not be as performant as it would be if the data types were the same.
March 3, 2009 at 3:36 am
Thanks Mangal, nice response, Just checking your blog now.
March 3, 2009 at 3:43 am
andrewd.smith (3/3/2009)
UPDATE C
SET C.rank = M.reputationvalue
FROM TblComputedData C INNER JOIN TblFeedbackData F
ON c.userId = F.UserId
INNER JOIN TblMasterData m
ON F.Score BETWEEN m.MinimumValue and M.MaximumValue
WHERE isProcessed =1
There's a potential problem with this update statement.
If the TblFeedbackData.SCORE (float) column holds a non-integer value that is between the numeric ranges defined in the TblMasterData table (e.g. 15.5), then the row associated with this user will not be updated.
The following amendment fixes this issue, though you may wish to round the non-integer score to the nearest integer rather than truncating it, by adding 0.5 before converting to an int.
UPDATE C SET C.rank = M.ReputationValueFROM TblComputedData C
INNER JOIN TblFeedbackData F
ON (C.userId = F.UserId)
INNER JOIN TblMasterData M
ON (CONVERT(int, F.Score) BETWEEN M.MinimumValue AND M.MaximumValue)
WHERE (F.IsProcessed = 1)
Also, what happens if the user's score is greater than the maximum MaximumValue in the TblMasterData table? Is this possible?
Because of the mismatch in datatypes between the TblFeedbackData.SCORE (float) and the TblMasterData.MinimumValue and TblMasterData.MaximumValue (int) columns, the join will not be as performant as it would be if the data types were the same.
Thanks dear,
yes, i think you pointed out two key issues, yes there is a every possibility of Score value being non-integer value. and it may also happen that user's score is greater than the maximum MaximumValue in the TblMasterData, but i guess we can have some more entries in TblMasterData to cater to that..... what do you think or there is any generic workaround to this ?
March 3, 2009 at 3:59 am
When writing an UPDATE ... FROM ..., it pays to write it as a SELECT first, to see what rows are going to be affected and what the new values are likely to be.
Start with a 'loose' SELECT which displays more rows than you want to update, to give you an idea of how the data looks:
SELECT c.*, f.[SCORE], m.[ReputationValue]
FROM #TblComputedData c
LEFT JOIN #TblFeedbackData f
ON f.[USERID] = c.[USERID] AND f.IsProcessed = 1
LEFT JOIN #TblMasterData m
ON f.[SCORE] BETWEEN m.[MinimumValue] AND m.[MaximumValue]
Results:
USERID Rank SCORE ReputationValue
----------- ----------- ---------------------- ---------------
1 NULL 90 6
2 3 35 4
3 8 NULL NULL
4 NULL NULL NULL
This shows that of the 4 users, 1 & 2 will be given a new Rank (ReputationValue). You can check that this is correct by referring back to the tables.
Now we tighten up the SELECT to return only the rows we want to update:
SELECT c.*, f.[SCORE], m.[ReputationValue]
FROM #TblComputedData c
INNER JOIN #TblFeedbackData f
ON f.[USERID] = c.[USERID] AND f.IsProcessed = 1
INNER JOIN #TblMasterData m
ON f.[SCORE] BETWEEN m.[MinimumValue] AND m.[MaximumValue]
Results:
USERID Rank SCORE ReputationValue
----------- ----------- ---------------------- ---------------
1 NULL 90 6
2 3 35 4
Note that only one of the two joins needs to be converted to an INNER JOIN, but inner joins are quicker than outer joins. This result contains only the rows we want to update and the value of [ReputationValue] is correct for each one.
Now convert the SELECT to an UPDATE:
UPDATE c SET Rank = m.[ReputationValue]
FROM #TblComputedData c
INNER JOIN #TblFeedbackData f
ON f.[USERID] = c.[USERID] AND f.IsProcessed = 1
INNER JOIN #TblMasterData m
ON f.[SCORE] BETWEEN m.[MinimumValue] AND m.[MaximumValue]
and finally check the result of the UPDATE by running our 'loose' SELECT:
SELECT c.*, f.[SCORE], m.[ReputationValue]
FROM #TblComputedData c
LEFT JOIN #TblFeedbackData f
ON f.[USERID] = c.[USERID] AND f.IsProcessed = 1
LEFT JOIN #TblMasterData m
ON f.[SCORE] BETWEEN m.[MinimumValue] AND m.[MaximumValue]
Results:
USERID Rank SCORE ReputationValue
----------- ----------- ---------------------- ---------------
1 6 90 6
2 4 35 4
3 8 NULL NULL
4 NULL NULL NULL
We run the 'loose' SELECT afterwards to look for any rows which were unintentionally affected by the update and to ensure that intentional updates (e.g. non-integer scores) were successful.
USERID Rank SCORE ReputationValue
----------- ----------- ---------------------- ---------------
1 6 90 6
2 4 35.5 4
3 8 NULL NULL
4 NULL NULL NULL
Hope this helps.
Cheers
ChrisM
[Edited to introduce a non-integer score]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 3, 2009 at 4:01 am
You could set the MaximumValue of the top ranked entry in the TblMasterData table to NULL and use the following amended query.
UPDATE C SET C.rank = M.ReputationValue
FROM TblComputedData C
INNER JOIN TblFeedbackData F
ON (C.UserId = F.UserId)
INNER JOIN TblMasterData M
ON (CAST(F.Score AS int) >= M.MinimumValue
AND (M.MaximumValue IS NULL OR CAST(F.Score AS int) <= M.MaximumValue))
WHERE (F.IsProcessed = 1)
Alternatively, and probably better, you could simply set the MaximumValue of the top ranked entry to the largest possible value for the data type (2147483647). You might also want to set the MinimumValue of the lowest ranked entry in the TblMasterData table to 0 rather than 1, or even -2147483648 if it's possible for a user to have a negative score.
March 3, 2009 at 4:17 am
lol 🙂 super Genius man.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply