Update one column with another columns value

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)

Update one column with another columns value

Author

Topic

dcummiskey
Starting Member

26 Posts

Posted - 2007-08-10 : 18:19:00

I have to update one column value with another columns' value based on
some conditions. I can return the columns that need to be updated but
I'm not sure how I would get the score value to update to the 1st
columns value, see query below:


UPDATE TBLRESULTSSTUDENTRAW SET SCORE = ??? WHERE TESTID = 2 AND UNIT
= 0 AND STUDENTID IN (

select studentid from tblResultsStudentRaw r where r.Score = 0 and 
r.testid = 2 and studentid in (select studentid from 
tblResultsStudentRaw where score > 0 and score is not null and testid = 1)
)

Basically, I want SET SCORE = ??? to be the value of the score for the
record returned from the query


select studentid from tblResultsStudentRaw r where r.Score = 0 
and r.testid = 2 and studentid in (select studentid from
 tblResultsStudentRaw where score > 0 and score is not null and testid = 1)

It's friday..i'm tired..maybe it'll make more sense to me monday.

thanks for your time,
Dan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-11 : 04:43:08

Well, since you have a WHERE for the Score to be ZERO, only students with ascore of 0 is returned.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-11 : 04:51:41

[code]UPDATE rsr
SET rsr.Score = x.Score1
FROM tblResultsStudentRaw AS rsr
INNER JOIN (
SELECT StudentID,
MAX(CASE WHEN TestID = 1 AND Score > 0 THEN 1 ELSE 0 END) AS Test1,
MAX(CASE WHEN TestID = 1 THEN Score ELSE 0 END) AS Score1,
MAX(CASE WHEN TestID = 2 AND Score = 0 THEN 1 ELSE 0 END) AS Test2
FROM tblResultsStudentRaw
GROUP BY StudentID
) AS x ON x.StudentID = rsr.StudentID
WHERE x.Test1 = 1
AND x.Test2 = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

dcummiskey
Starting Member

26 Posts

Posted - 2007-08-15 : 10:59:37

That worked. Thanks for the help.

- Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources