MS Access - Update and Inner Join with Multiple Rows-CodePudding

I found answer for this problem foe SQL Server but not MS Access.

Table NE:

IP           | Status | Peer
-----------------------------
10.10.10.1   | 0      | null
10.20.1.5    | 0      | null

Table Peer:

IP           | Peer             | Status
------------------------------------------
10.10.10.1   | 10.100.2.3       | 0
10.10.10.1   | 10.200.1.1       | 1
10.10.10.1   | 10.50.100.7      | 10
10.20.1.5    | 10.20.20.7       | 10
10.20.1.5    | 10.2.2.10        | 5

Desired result:

IP           | Status | Peer
-----------------------------
10.10.10.1   | 10     | 10.50.100.7
10.20.1.5    | 10     | 10.20.20.7

Sometimes this query works and sometimes it fails:

Update NE
Inner join (Select * from Peer Order by Status Desc) Q
On Q.IP = NE.IP
Set NE.Status = Q.Status, NE.Peer = Q.Peer
Where NE.Status = 0

That query pops up a notification (for example) that 3 rows will be affected for IP 10.10.10.1. Which one will be kept?

When I change Order by Status Desc to Order by Status Asc the result does not change.

CodePudding user response：

Need a unique identifier field in Peer table - an autonumber will serve.

Do not see that NE table is even needed to produce the desired output.

SELECT * FROM Peer WHERE ID IN (
   SELECT TOP 1 ID FROM Peer AS Dup WHERE Dup.IP = Peer.IP ORDER BY Dup.Status DESC, Dup.ID DESC);

But if you must UPDATE NE:

UPDATE NE 
INNER JOIN(
    SELECT * FROM Peer WHERE ID IN (
        SELECT TOP 1 ID FROM Peer AS Dup WHERE Dup.IP = Peer.IP ORDER BY Dup.Status DESC, Dup.ID DESC)) AS Q1 
ON NE.IP = Q1.IP SET NE.Status = [q1].[Status], NE.Peer = [q1].[Peer];