1

I've got a table with people who've entered to win a truck.

tbl_contestants

Contestants can enter up to 100 times. I want to update the entry_type on all records where the user has already entered 100 or more times.

I want to update all records where 100 or more records have email address of X. I need to determine X by getting a count of records with a repeating email address then save those records and update all of their entry_type.

I'm using the email address as my selector since all entries have email addresses (required to enter).

Would I select a count somewhere in the query? This one has me baffled.

I've since updated the logic on the web form that limits any new entries to 100, I just need to deal with entries that occurred before we updated the logic.

Edit

I don't know the email addresses of all the entrants that have reached the 100 threshold. We're talking about a DB with 320K records. I need to figure out which email addresses occur over 100 times and then set entry_type to 100 on all of those records that met the 100 threshold.

Ofeargall
  • 5,340
  • 5
  • 28
  • 33
  • `UPDATE tbl SET entry_type = 'BAD'` ... `UPDATE tbl SET entry_tpe = 'GOOD' WHERE email = 'the@email.com' LIMIT 100` – Brock Hensley Apr 10 '14 at 00:04
  • relevant? http://stackoverflow.com/questions/1895110/row-number-in-mysql – wils484 Apr 10 '14 at 00:39
  • that table have any primary key field show the field name – Sathish Apr 10 '14 at 04:12
  • The records have an auto increment unique id number as the PK – Ofeargall Apr 10 '14 at 04:13
  • Ya, I just updated the title to accurately reflect the question: "Update records where 100 or more records have email address of X". Sorry to be so vague but the more question people ask the closer I am to phrasing the question properly. – Ofeargall Apr 10 '14 at 04:18

2 Answers2

2

Try Like this

UPDATE tbl_contestants SET entry_type = 'GOOD' from  tbl_contestants WHERE email IN 
(select email from tbl_contestants group by email HAVING COUNT(*) >=100)

Or

UPDATE tbl_contestants SET entry_type = 'GOOD'from  tbl_contestants WHERE email IN 
(select email from tbl_contestants where email='X' group by email 
HAVING COUNT(*) >=100)
Sathish
  • 4,419
  • 4
  • 30
  • 59
  • Query 1 gets me an error code: "Error Code 1093: You can't specify target table `tbl_contestants` for update in FROM clause". Query 1 definitely looks like the correct logic I'm going for... – Ofeargall Apr 10 '14 at 04:57
  • @Ofeargall yes i am missed from clause now correct it check it – Sathish Apr 10 '14 at 05:49
0

You could set the entry_type to BAD for all existing entries for that email:

UPDATE tbl_contestants SET entry_type = 'BAD' WHERE email = 'X'

Then go back and set the entry_type to GOOD for the email, with a limit of 100:

UPDATE tbl_contestants SET entry_type = 'GOOD' WHERE email = 'X' LIMIT 100

Drawback being this would have to be ran for each email address, but it's simple.

Brock Hensley
  • 3,617
  • 2
  • 29
  • 47
  • Brock, I failed to mention that I don't know the email addresses of all the entrants that have reached the 100 threshold. We're talking about a DB with 320K records. I need to figure out which email addresses occur over 100 times and then set entry_type to 100 on all of those records that met the 100 threshold. I'll update my question to include that... – Ofeargall Apr 10 '14 at 03:59