I need some help with a simple (simple in PostgreSQL, but I need in SQL Server 2008) update from select statement. I don't know how to pass "values" from rows as variables to the select statement in from clause. This is what I've done.
DECLARE @OldAnswerValue INT = 7;
DECLARE @Type INT =3;
SELECT A.Id as NewAnswer
FROM tblEvaluationAnswers A
WHERE A.AnswerValue=
CASE @OldAnswerValue
WHEN 0 THEN 1
WHEN 1 THEN 2
...
WHEN 9 THEN 5
END
AND AnswerType=@Type
and my update will look like
UPDATE transPersonEvaluation
SET UserAnswer=PEA.NewAnswer
FROM ( ) as PEA --here the select statement inside from
but I don't know how to pass @OldAnswerValue and @Type as parameters into the from clause. This values are extracted from each row of transPersonEvaluation which I am updating. In PostgreSQL I think it is like
UPDATE transPersonEvaluation PE
SET UserAnswer=PEA.NewAnswer
FROM (
SELECT A.Id as NewAnswer
FROM tblEvaluationAnswers A ... where A.AnswerType=PE.AnswerType) as PEA;
but it is throwing syntax error on SQL Server 2008.
Any help would be appreciated, Thanks!
in sql server your query would look like this.
UPDATE PE
SET PE.UserAnswer=PEA.NewAnswer
FROM transPersonEvaluation PE
JOIN
(
SELECT A.Id as NewAnswer
FROM tblEvaluationAnswers A ... ) as PEA ON PEA.AnswerType = PE.AnswerType;