Help needed update Query - 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 2005 Forums

Transact-SQL (2005)

Help needed update Query

Author

Topic

zubairmasoodi
Starting Member

35 Posts

Posted - 2009-03-03 : 04:03:31

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

With regards
Zubair Masoodi
(Every day's a school day)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-03 : 04:17:29

if i've understood you correctly...


update c
	set [Rank] = ReputationValue
from dbo.TblFeedbackData f
	join dbo.TblMasterData m
		on f.SCORE between m.MinimumValue and m.MaximumValue
	join dbo.TblComputedData c 
		on c.USERID = f.USERID

zubairmasoodi
Starting Member

35 Posts

Posted - 2009-03-03 : 04:52:06

quote:

Originally posted by elancaster

if i've understood you correctly...


update c
	set [Rank] = ReputationValue
from dbo.TblFeedbackData f
	join dbo.TblMasterData m
		on f.SCORE between m.MinimumValue and m.MaximumValue
	join dbo.TblComputedData c 
		on c.USERID = f.USERID

You are real genius man, my sincere thanks to you, i never knew we can have statements like this

join dbo.TblMasterData m
on f.SCORE between m.MinimumValue and m.MaximumValue

thanks dear, i just need to impose a check to perform this update on feilds for which IsProcessed is 1 and update the same with 2 when done

With regards
Zubair Masoodi
(Every day's a school day)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-03 : 04:56:19

no prob

i'm assuming you'll be ok with the second bit then? come back if not

Em

zubairmasoodi
Starting Member

35 Posts

Posted - 2009-03-03 : 05:09:46

quote:
Originally posted by elancaster

no prob

i'm assuming you'll be ok with the second bit then? come back if not

Em

Yeah there was nothing more left. But i want to correct myself , i didnt see your profile before posting my reply, here i stand corrected..

you are a real genius , "cute" girl :)

With regards
Zubair Masoodi
(Every day's a school day)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-03 : 05:22:00

lol

...happens all the time

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources