SQL Server - update a column based on a different column value

Question

How to update a column in a TableA value found in another table, Table B , depending on another column, Type , in TableA

eg.

Table a

 Location Type Value USA Dog 20 UK Cat 30 

Table B

 Dog Cat Rabbit 50 70 100 

Logics:

• If tableA.Value = Dog update TableA.Value = TableB.Dog
• If tableA.Value = Cat , then update TableA.Value = TableB.Cat
• If tableA.Value = Rabbit , then update TableA.Value = TableB.Rabbit

Note. There are only 3 options, so hard coding is fine.

Result

Table a

 Location Type Value USA Dog 50 UK Cat 70 

Answer 1

How about something like

 UPDATE TableA SET Value = CASE Type WHEN 'DOG' then B.Dog WHEN 'CAT' then B.Cat WHEN 'RABBIT' then B.Rabbit ELSE Value END FROM TableB b 

SQL Fiddle DEMO