How to update with more condition using join table in SQL Server?

Question

I have two table with data sample, i want to update data in TableA with condition . TableA sample

ID  Product     Price
001 Apple       Null
002 Orange      Null

TableB Sample

ID  Product     Type    Price
001 Apple       H       10
001 Apple       M       7
002 Orange      M       8
003 Banana      H       20
003 Banana      M       14

And i try with as follow

Update A
Set A.Price = isnull( (select B.Price where B.Type = 'H') , isnull(select B.Price where B.Type = 'M', 0))
from TableA A inner join TableB B on A.ID = B.ID

I want result as

ID  Product     Price
001 Apple       10
002 Orange      8

But it's not working

Answer 1

Try this

DECLARE @TableA AS TABLE(ID INT, Product VARCHAR(20),Price INT)
INSERT INTO @TableA
SELECT 001,'Apple',       Null UNION ALL
SELECT 002,'Orange',      Null

DECLARE @TableB AS TABLE (ID INT,Product VARCHAR(20),[Type] VARCHAR(2),Price INT)
INSERT INTO @TableB
SELECT 001,'Apple'  ,'H',10 UNION ALL
SELECT 001,'Apple'  ,'M',7  UNION ALL
SELECT 002,'Orange' ,'M',8  UNION ALL
SELECT 003,'Banana' ,'H',20 UNION ALL
SELECT 003,'Banana' ,'M',14



UPDATE A
SET A.Price = ISNULL(CASE B.[Type] WHEN 'H' THEN B.Price
                                    WHEN 'M' THEN B.Price END,0)
FROM @TableA A
INNER JOIN @TableB B ON A.ID=B.ID

SELECT * FROM @TableA

Result

ID  Product Price
----------------
1   Apple   10
2   Orange  8