0

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 wich 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!

Cristhian Valencia
  • 35
  • 1
  • 8
  • 1
    Possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) – Sean Lange Nov 09 '15 at 17:01

1 Answers1

0

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;
JamieD77
  • 13,796
  • 1
  • 17
  • 27