USE tempdb
GO

DECLARE @A TABLE
(
  ID TINYINT,
  TYPE VARCHAR(10)
)
INSERT @A(ID,TYPE)
SELECT 1,'Не задан'
 UNION ALL
SELECT 2,'Не задан'
  UNION ALL
SELECT 3,'Не задан' 
  UNION ALL
SELECT 4,'Не задан'
  UNION ALL
SELECT 5,'Не задан'
  UNION ALL
SELECT 6,'Не задан'

DECLARE @B TABLE
(
  ID TINYINT,
  IDTYPE TINYINT 
)

INSERT @B(ID,IDTYPE)
 SELECT 1, 1
   UNION ALL
SELECT 2,1
   UNION ALL
SELECT 3, 0
   UNION ALL
SELECT 4,3
   UNION ALL
SELECT 5, 5
   UNION ALL
SELECT 6, 0

UPDATE TT SET 
 TT.TYPE=
  CASE 
    WHEN X.IDTYPE>1 THEN 'ПОЗИЦИЯ>'+CAST(X.IDTYPE AS CHAR(3))
	WHEN X.IDTYPE=1 THEN 'ОТКРЫТ'
	WHEN X.IDTYPE=0 THEN 'ЗАКРЫТ'
  END
 FROM @A AS TT
 JOIN @B AS X ON TT.ID=X.ID 

 SELECT * FROM @A;