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

Please post your table structures. Also, Sara's max ID is 789, correct? — sgeddes
– sgeddes, Commented Feb 26, 2013 at 1:51

sgeddes · Accepted Answer · 2013-02-26 02:33:35Z

3

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

edited Feb 26, 2013 at 2:33

answered Feb 26, 2013 at 2:02

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hadenp Over a year ago

Fred's ID should be something else...sorry. Would you be able to restate this so that the query simply selects the records in donations that it is going to update with the member's max ID?

hadenp Over a year ago

Thank you sgeddes - Before I actually do the update, I'd like to know the following: For all donations IDs, are there multiple IDs in Members? Before I can update Sara's IDs in donations to 789, I need to know all of her IDs in Members, then use the max to update her records in Donations.

Andrew FigPope · Accepted Answer · 2013-02-26 01:52:07Z

0

Try SELECT MEMBERS.* FROM MEMBERS, DONATIONS WHERE DONATIONS.ID != MEMBERS.ID it will give you all of the rows in members that have an ID that isn't in donations. My syntax might be a little off, but refer to documentation about joins, like this piece from informIT for more info.

answered Feb 26, 2013 at 1:52

Andrew FigPope

932 silver badges8 bronze badges

1 Comment

Barmar Over a year ago

He wants all the members that are in donations, as well as all their other IDs.

Barmar · Accepted Answer · 2013-02-26 03:34:45Z

0

This will find the max member ID for all the extra duplicate IDs:

select m1.member_id, max(m2.member_id) maxid
from members m1
join members m2 on m1.name = m2.name and m1.member_id < m2.member_id

To restrict it to just users who are in donations:

select m1.member_id, max(m2.member_id) maxid, count(*) duplicates
from members m1
join (select distinct member_id from donations) d on m1.member_id = d.member_id
join members m2 on m1.name = m2.name and m1.member_id < m2.member_id
having duplicates > 1

edited Feb 26, 2013 at 3:34

answered Feb 26, 2013 at 1:58

Barmar

789k57 gold badges555 silver badges669 bronze badges

1 Comment

hadenp Over a year ago

Hi Barmar. I have duplicates in Members and some donations could have been made under a different member's ID that really belongs to one person in Members. What I'd like to know is if any donations records exist that point to more than 1 record in Members.

Collectives™ on Stack Overflow

SQL - How to select multiple rows

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related