Geek asks and answers

SQL - How to select multiple rows

I have two tables, members and donations related by member id. Members have many duplicates. I want to delete them, but before I do this, I want to update the entries of the donation members to a single ID - perhaps the value is MAX (456) in the case of Sara Tam.

Is there a request to select all members of Sara (and others who have entries in Donations but not Fred, who do not? How can I link identifiers 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
+5
source share
3 answers

If I understand your questions correctly, you want to UPDATE your donation table to the MAX identifier associated with the member, and DELETE duplicate entries in the Members table, keeping the MAX.

, - 2 :

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

, , SQL, MAX (Id). , :

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;

+3

SELECT MEMBERS.* FROM MEMBERS, DONATIONS WHERE DONATIONS.ID != MEMBERS.ID, , , . , , informIT .

0

:

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

, :

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
0

More articles:


All Articles