If I understand the requirements correctly, it would look something like this:

IF OBJECT_ID(N'tempdb..#T1') IS NOT NULL

DROP TABLE #T1;

IF OBJECT_ID(N'tempdb..#T2') IS NOT NULL

DROP TABLE #T2;

CREATE TABLE #T1 ([Number] VARCHAR(10));

CREATE TABLE #T2

([OldNumber] VARCHAR(10),

[NewNumber] VARCHAR(10));

INSERT INTO #T1

(Number)

VALUES ('10'),

('20'),

('30'),

('40');

INSERT INTO #T2

(OldNumber, NewNumber)

VALUES ('10', '15'),

('20', 'MISSING'),

('30', NULL);

SELECT *

FROM #T1;

MERGE INTO #T1 AS Tgt

USING

(SELECT #T2.OldNumber,

#T2.NewNumber

FROM #T2) AS Src

ON Tgt.Number = Src.OldNumber

WHEN MATCHED AND Src.NewNumber IS NOT NULL

AND Src.NewNumber != 'MISSING'

THEN UPDATE

SET Number = Src.NewNumber;

SELECT *

FROM #T1;

Does that help?