asked on

MySQL - Update a column based on a select query

I have a MySQL database with a table called 'results'. It has race results from various years.

I have a query that returns a list of participants who appear 3 times in the table (the difference being the 'year' column)

Select COUNT(firstname) AS CountOfRuns,
firstname,
surname,
dob, email
from results
group by firstname,
surname,
dob
having CountOfRuns = 3

Open in new window

I have a column called 'pin' and for each of the rows returned by the above query (there will be 3 rows per person) I want to update the 'pin' column to have the number 3 in it.

I can't get my head around how to get the Update statement to make the necessary change.

HainKurt

please post the data before and after update
also, post data from above query for that person (3 rows)

Chris Millard

ASKER

OK - so this would be one persons data in the table who has run for 3 years:-

bib      surname      firstname      category            age      time            position      gender      email                                    dob                  year      pin
58      Smith            John            MV40            44      02:45:58            1            Male      john.smith@doesntexist.com      18/08/1970      2015      0
259      Smith            John            MV40            45      02:54:34            3            Male      john.smith@doesntexist.com      18/08/1970      2016      0
271      Smith            John            MV40            46      02:59:49            3            Male      john.smith@doesntexist.com      18/08/1970      2017      0

If I run the query above, his data would be returned as:-

CountOfRuns      firstname      surname      dob                  email
3                        John            Smith            18/08/1970      john.smith@doesnexist.com

As his CountOfRuns = 3, I want his 'pin' data in the database to be updated to read:-

bib      surname      firstname      category            age      time            position      gender      email                                    dob                  year      pin
58      Smith            John            MV40            44      02:45:58            1            Male      john.smith@doesntexist.com      18/08/1970      2015      3
259      Smith            John            MV40            45      02:54:34            3            Male      john.smith@doesntexist.com      18/08/1970      2016      3
271      Smith            John            MV40            46      02:59:49            3            Male      john.smith@doesntexist.com      18/08/1970      2017      3

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

if you have some id column in results table, you should use this

update results r inner join (
 Select id
   from results
  group by id
 having count(1)= 3
) s on s.id=r.id
set r.pin=3

Open in new window

update results r
set r.pin=3
where id in (
 Select id
   from results
  group by id
 having count(1) = 3
)

Open in new window

Chris Millard

ASKER

That has worked perfectly. Thanks.

HainKurt

if you have customer id column in results table, use the queries @ ID: 42251676

should be much much efficient...