sql-serverselectsql-update

How to pass row value in update from select statement?


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!


Solution

  • 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;