How to update all the mid to min based on eid present in other table

Question

0.00/5 (No votes)

See more:

mbr table

eid     mid
1       15           for eid 1, mid 14 is the min value
1       14
2       18           for eid 2, mid 17 is the min value
2       17
3       19           for eid 3, mid 19 is the min value
3       20

nm table

mid     nmid
15      100
14      104
18      109
17      108
19      999
20      110


expected output

nm table

mid     nmid
14      100   
14      104
17      109   
17      108
19      999
19      110    


for eid 1,mid 14 is min compared to 15 so it should update 15 to 14 in nm table 
for eid 2,mid 17 is min compared to 18 so it should update 18 to 17 in nm table
for eid 3,mid 19 is min compared to 20 so it should update 20 to 19  in nm table

What I have tried:

i tried with logic of selecting max mid from mbr table and select min mid from nm table and use update query to update max to min.it is updating every thing.
can you please help[ on this.

please let me know if any information needed

Posted 4-Feb-20 7:45am

nithin793

Updated 4-Feb-20 9:43am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-02-04T09:44:00

Check this out:

SQL

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:

For further details, please, see: How do I UPDATE from a SELECT in SQL Server? - Stack Overflow[^]