SELECT unique row from field 3 to field 4

Is there a way to delete duplicate records based on two fields?

I have a system where people can register for sport events. In the table:

event_registrations

• unique_id

• eventname

• id (person's id number)

• Name and Surname

One person can apply for many events - id may duplicate an event may have multiple participants - eventname may duplicate:

--Johnsmith--  --Mountain Cycle--
--Johnsmith--  --Marathnon Walk--

--Linda--      --Mountain Cycle--
--Johnsmith--  --Mountain Cycle--

But a person may not register for a event they have already registered for:

--Johnsmith--   --Mountain Cycle--
--J开发者_如何学JAVAohnsmith--   --Mountain Cycle--

They Select a event name through a form. Then the form data and their user details is stored in table event_registrations.

Any help would be appreciated

---

First delete any rows with duplicate (eventname, id) combinations.

Then add the UNIQUE constraint:

ALTER TABLE yourTable
  ADD CONSTRAINT eventname_person_Unique
    UNIQUE INDEX eventname_id_U 
       (eventname, id) ;

Your form that adds registrations should be adjusted accordingly to treat the error it will get from MySQL when a duplicate row is rejected.

---

A UNIQUE INDEX is the way to prevent this, as ypercube suggests. To identify/delete existing duplicates you could use this:

SELECT
    eventname,
    id    -- You should consider using a less ambiguous name here
FROM
    Event_Registrations ER1
WHERE
    EXISTS (
        SELECT *
        FROM Event_Registrations ER2
        WHERE
            ER2.eventname = ER1.eventname AND
            ER2.id = ER1.id AND
            (ER2.registration_datetime < ER1.registration_datetime OR
                (ER2.registration_datetime = ER1.registration_datetime AND
                 ER2.unique_id < ER1.unique_id
                )
            )
    )

---

If you need to do some data tidy up before adding the unique constraint then you could use the following (one good reason why always having a unique id column is a good idea):

create table id_for_deletion (id int unsigned not null);

insert into id_for_deletion (id) 
( 
select a.delete_me_id
from (
select eventname,id,max(unique_id) as delete_me_id
from event_registrations
group by eventname,id
having count(*) > 1
) a);



delete from event_registrations where unique_id in (select id from id_for_deletion);

drop table id_for_deletion;