CODEWITHSUNDEEP
sqlsql-serversql-update
celoaga
celoaga

Reputation: 140

SQL Server : update a value within the same table

I want to use an update command for the table below. What I want to do is to update the answer column based on the reference column: 2nd and 3rd answers should be A because their reference is 1 and answer of the 1st row is A, similarly 5 and 6 should be X.

Sample data:

id    answer  ref
-------------------
1       A      1
2       B      1
3       C      1
4       x      4
5       y      4
6       z      4

Upvotes: 1

Views: 55

Answers (3)

Serif Emek
Serif Emek

Reputation: 674

update a
set a.answer = b.answer
from tablename a, tablename b
where a.ref = b.id

edit: adding this also may be better;

and a.ref <> a.id

Upvotes: 2

Abiud Orina
Abiud Orina

Reputation: 1232

You can use the case statement

UPDATE sample_data
SET answer = ref
WHERE CASE 
      WHEN ref = 1
      THEN answer = 'A' 
      ELSE
      WHEN REF = 4
      THEN ANSWER = 'x'
END as available

Upvotes: 0

Aditi Singh
Aditi Singh

Reputation: 117

update table1 t1 set answer = t2.answer from table1 t2 where t1.ref = t2.id;

Upvotes: 1

Related Questions