How do I update latest mid in nm table based on latest date present for that mid in mbr table.

Question

0.00/5 (No votes)

See more:

mbr table

eid      date              mid
1        13/01/2019        15           for eid 1,14 is mid for latest date
1        16/02/2019        14
2        22/06/2019        18           for eid 2,18 is mid for latest date 
2        25/01/2018        17
3        19/05/2019        19           for eid 3,20 is mid for latest date 
3        22/08/2019        20
4        22/09/2019        44           for eid 4,44 is mid for latest date 
4        25/12/2018        49
6        19/05/2019        55           for eid 6,94 is mid for latest date 
6        22/08/2019        94

nm table

mid     nmid
15      100
14      104
18      109
17      108
19      999
20      110
44      222
49      598
55      335
94      195



expected output

nm table

select * from nm where mid in(select mid from mbr where eid in(1,2,4))

mid     nmid
14      100   
14      104
18      109   
18      108
44      222
44      598    


for eid 1,mid 14 is latest when compared to 15 so it should update 15 to 14 in nm table 
for eid 2,mid 18 is latest when compared to 17 so it should update 17 to 18 in nm table
for eid 4,mid 44 is latest when compared to 49 so it should update 49 to 44 in nm table


***The query should update nm table for only specific eid's in mbr table(in our example it should update nm table only for mid's with eid=1,2,4 in mbr table and remaining mid should be unchanged in nm table) and get output in nm table.

What I have tried:

i tried with logic of selecting max date from mbr table and mid for that date and update that mid in nm table.but it is updating with same mid for all.
can you please help on this.

please let me know if any information needed

Posted 25-Feb-20 17:23pm

nithin793

Updated 26-Feb-20 1:24am

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)

Richard Deeming · Answer 1 · 2020-02-26T01:25:00

Solution 1

It's not entirely clear what the relationship is between your tables, or what the conditions are for updating. But something like this should get you started:

SQL

UPDATE
    N
SET
    mid = ML.mid
FROM
    nm As N
    INNER JOIN mbr As M
    ON M.mid = N.mid
    CROSS APPLY
    (
        SELECT TOP 1 mid
        FROM mbr As M2
        WHERE M2.eid = M.eid
        ORDER BY M2.[date] DESC
    ) As ML
WHERE
    N.mid != ML.mid
;

SQL Server CROSS APPLY and OUTER APPLY[^]

Posted 26-Feb-20 1:25am

Richard Deeming

Comments

nithin793 26-Feb-20 22:10pm

Can this be used with out cross apply?

Richard Deeming 27-Feb-20 5:55am

Possibly, depending on what version of SQL Server you're using.

But what's the problem with using APPLY?

nithin793 28-Feb-20 0:24am

Im getting sql code -727,sql state:56098,SQLERRMC 2:-10442601;APPLY|om mbr aCROSS|JOIN

nithin793 28-Feb-20 0:25am

Im working on squirrel client version 3.3.0