Check this out:
DECLARE @mbr TABLE(eid INT, mid INT)
INSERT INTO @mbr(eid, mid)
VALUES(1, 15), (1, 14), (2, 18),
(2, 17), (3, 19), (3, 20)
DECLARE @nm TABLE(mid INT, nmid INT)
INSERT INTO @nm(mid, nmid)
VALUES(15, 100), (14, 104), (18, 109),
(17, 108), (19, 999), (20, 110)
UPDATE t1
SET t1.mid = t2.minmid
FROM @nm AS t1
INNER JOIN (
SELECT eid, mid, MIN(mid) OVER(PARTITION BY eid ORDER BY mid) AS minmid
FROM @mbr
) AS t2 ON t1.mid = t2.mid
SELECT *
FROM @nm
Result:
mid nmid
14 100
14 104
17 109
17 108
19 999
19 110
For further details, please, see:
How do I UPDATE from a SELECT in SQL Server? - Stack Overflow[
^]