SQL - How to select multiple rows

Question

I've have two tables, Members and Donations linked by a member ID. Members has numerous duplicates. I want to remove them, but before I do, I want to update a member's entries in Donations to a single ID - probably the MAX value (456) in the case of Sara Tam.

Is there a query to select all of Sara's member (and others who have entries in Donations but not Fred who doesn't? How can I associate IDs 123 and 456?

   members          donations
    -----------     -----------
    123 Sara Tam        123   20.00   
    456 Sara Tam        123   40.00 
    789 Sara Tam        333   10.00
     .                  444   30.00 
     .                  999   30.00 
    789 Fred Foo

Answer 1

If I'm understanding your questions correctly, you want to UPDATE your Donations table to the MAX Id associated with a Member, and the DELETE the duplicated records in the Members table keeping the MAX.

If so, then this should work -- however, you shouldn't have 2 Members with the same id:

UPDATE Donations D
  JOIN Members M ON M.MemberId = D.MemberId
  JOIN (SELECT Max(MemberId) MaxId, Name
        FROM Members 
        GROUP BY Name
      ) M2 ON M.Name = M2.Name
SET D.MemberId = M2.MaxId;

DELETE M
FROM Members M
  JOIN Members M2 ON M.Name = M2.Name AND M.MemberId < M2.MemberId;

SQL Fiddle Demo

Give your comments, perhaps you are only looking for the SQL statement to show the updated Donations with MAX(Id). If so, then this should work:

SELECT M2.MaxId MemberId, D.Amount
FROM Donations D
  JOIN Members M ON M.MemberId = D.MemberId
  JOIN (SELECT Max(MemberId) MaxId, Name
        FROM Members 
        GROUP BY Name
      ) M2 ON M.Name = M2.Name;

And the updated fiddle